Incrementing a value in a stored procedure

no need for order by...
and I dont see any issu here to create that result...
try below query, if same thing happens try to increment the parameter first and use @QB_InvoiceNumber in your query instead of @QB_InvoiceNumber + 1 after

set @QB_InvoiceNumber = @QB_InvoiceNumber +1

INSERT INTO [360_InvoiceTemp] 
      (InvoiceID, ClientID, CompanyID, SiteID, InvoiceDate, QB_InvoiceNumber, 
       InvoiceTotal, InvoiceTax, Comments, UserID, Name) 
SELECT InvoiceID, ClientID, CompanyID, SiteID, InvoiceDate, @QB_InvoiceNumber + 1, 
       InvoiceTotal, InvoiceTax, Comments, @UserID, Name 
  FROM [360_Invoice] 
 WHERE [360_Invoice].Type = 'Template' 
   AND [360_Invoice].InvoiceStatus = 'Active' 
   AND [360_Invoice].BillingInterval = @BillingInterval 
   AND [360_Invoice].InvoiceDate <= @InvoiceDate 
   AND [360_Invoice].InvoiceTotal <> '0.00'

Select allOpen in new window

Hi
Replace :
QB_InvoiceNumber = @QB_InvoiceNumber + 1
with following in your query:
QB_InvoiceNumber = @QB_InvoiceNumber +  ROW_NUMBER() OVER (order by [360_Invoice].InvoiceID)
This should work. Make sure that u r using SQL Server 2005 or higher.
Thanks
Vipin Goel

First,if the purpose is just to increment the  "qb_invoicenumber" why don't you use the IDENTITY definition? So in your table creation or definition, you can set your " qb_invoicenumber" column like this:
 qb_invoicenumber INT IDENTITY(1,1);in this case, at each insert, your  qb_invoicenumber will be increase of 1 beginning to 1;
for eg: first invoice :  qb_invoicenumber will be 1, second invoice: qb_invoicenumber will be 2, all this in an automated way.This saves you one parameter in your procedure, and one field to not care about in your stored procedure -->saved time and faster execution.

But if you still want to make the calculation as previously in your post, try this below:

CREATE PROCEDURE [dbo].[Xpsgetinvoicequeue]
                -- Add the parameters for the stored procedure here
                @BillingInterval INT,
                @UserID           VARCHAR(50),
                @QB_InvoiceNumber INT,
                @InvoiceDate      DATETIME
AS
  BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET nocount  ON;
     
    -- Insert statements for procedure here
    INSERT INTO [360_InvoiceTemp]
               (invoiceid,
                clientid,
                companyid,
                siteid,
                invoicedate,
                qb_invoicenumber,
                invoicetotal,
                invoicetax,
                comments,
                userid,
                name)
    SELECT   invoiceid,
             clientid,
             companyid,
             siteid,
             invoicedate,
             @QB_InvoiceNumber + 1, --here is where  i made the change--
             invoicetotal,
             invoicetax,
             comments,
             @UserID,
             name
    FROM     [360_Invoice]
    WHERE    [360_Invoice].TYPE = 'Template'
             AND [360_Invoice].invoicestatus = 'Active'
             AND [360_Invoice].billinginterval = @BillingInterval
             AND [360_Invoice].invoicedate <= @InvoiceDate
             AND [360_Invoice].invoicetotal <> '0.00'
    ORDER BY [360_Invoice].invoiceid
  END

GO

Hope this will bring you the solution...Cheers!

Thank you all, but nothing worked

can you please run this and tell us what you get


declare @QB_InvoiceNumber
set @QB_InvoiceNumber = 1

SELECT InvoiceID, ClientID, CompanyID, SiteID, InvoiceDate, @QB_InvoiceNumber + 1,  
       InvoiceTotal, InvoiceTax, Comments, @UserID, Name  
  FROM [360_Invoice]  
 WHERE [360_Invoice].Type = 'Template'  
   AND [360_Invoice].InvoiceStatus = 'Active'  
   AND [360_Invoice].BillingInterval = @BillingInterval  
   AND [360_Invoice].InvoiceDate <= @InvoiceDate  
   AND [360_Invoice].InvoiceTotal <> '0.00'

Kurt, the table has no rows as it stands now, why would you want me to run a select statement?

because you say you get 1 in all columns,
this query should return 2 for @QB_InvoiceNumber + 1

"if I pass @QB_InvoiceNumber as 1 I get 1 in all records, what am I doing wrong?" I want to understand whats happening first before suggesting something...

Yes, I am passing 0 as the parameter and it is returning 1 on all records, I want it to increment it as it inserts records

:) before you said you are passing 1 and it is setting all 1, now you say you are passing 0...
misleading all of us...

did you try this

QB_InvoiceNumber = @QB_InvoiceNumber +  ROW_NUMBER() OVER (order by [360_Invoice].InvoiceID)

INSERT INTO [360_InvoiceTemp]  
      (InvoiceID, ClientID, CompanyID, SiteID, InvoiceDate, QB_InvoiceNumber,  
       InvoiceTotal, InvoiceTax, Comments, UserID, Name)  
SELECT InvoiceID, ClientID, CompanyID, SiteID, InvoiceDate, 
       @QB_InvoiceNumber - 1 + (ROW_NUMBER() OVER (order by [360_Invoice].InvoiceID)),
       InvoiceTotal, InvoiceTax, Comments, @UserID, Name  
  FROM [360_Invoice]  
 WHERE [360_Invoice].Type = 'Template'  
   AND [360_Invoice].InvoiceStatus = 'Active'  
   AND [360_Invoice].BillingInterval = @BillingInterval  
   AND [360_Invoice].InvoiceDate <= @InvoiceDate  
   AND [360_Invoice].InvoiceTotal <> '0.00'

Select allOpen in new window

Kust, I am sorry about that, it was a typo. I tried that and it says that it does not recognize the function ROW_NUMBER.  it is passing the correct parameter and it adds 1 to it the first time but then it does not increment after that.

do you get error when you run this?

SELECT InvoiceID, ClientID, CompanyID, SiteID, InvoiceDate,  
       ROW_NUMBER() OVER (order by [360_Invoice].InvoiceID) as RowID,
       InvoiceTotal, InvoiceTax, Comments, Name  
  FROM [360_Invoice]  
 WHERE [360_Invoice].Type = 'Template'  
   AND [360_Invoice].InvoiceStatus = 'Active'  
   AND [360_Invoice].InvoiceTotal <> '0.00'

Yes:

Server: Msg 195, Level 15, State 10, Line 2
'ROW_NUMBER' is not a recognized function name.

are you sure you are using 2005 SQL Server (I mean DB version, not management studio)?

http://msdn.microsoft.com/en-us/library/ms186734(SQL.90).aspx

in case you have previous version you should implement this:
http://www.sqlteam.com/article/returning-a-row-number-in-a-query

Kurt,
I am not sure, I am not the DBA, we may be using 2003

Hi,


Please make the Invoice No as identity.

Make identity OFF

 INSERT INTO [360_InvoiceTemp]
(InvoiceID, ClientID, CompanyID, SiteID, InvoiceDate, QB_InvoiceNumber, InvoiceTotal, InvoiceTax, Comments, UserID, Name) SELECT InvoiceID, ClientID, CompanyID, SiteID, InvoiceDate, QB_InvoiceNumber = @QB_InvoiceNumber + 1, InvoiceTotal, InvoiceTax, Comments, @UserID, Name FROM [360_Invoice] WHERE [360_Invoice].Type = 'Template' AND [360_Invoice].InvoiceStatus = 'Active' AND [360_Invoice].BillingInterval = @BillingInterval AND [360_Invoice].InvoiceDate <= @InvoiceDate AND [360_Invoice].InvoiceTotal <> '0.00' ORDER BY [360_Invoice].InvoiceID


Make Identity ON