?
Solved

Incrementing a value in a stored procedure

Posted on 2009-12-20
17
Medium Priority
?
221 Views
Last Modified: 2012-05-08
Hello,
I need to insert a value in a table so it is incremented by 1 based on a parameter passed to the SP, here is what I did but if I pass @QB_InvoiceNumber as 1 I get 1 in all records, what am I doing wrong?
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 = @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
END
GO
0
Comment
Question by:htabbach
17 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 26091865
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'

Open in new window

0
 
LVL 3

Expert Comment

by:vipin_nagarro
ID: 26091969
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
0
 
LVL 5

Expert Comment

by:spikelly
ID: 26092039
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!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:htabbach
ID: 26092460
Thank you all, but nothing worked
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26092491
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'
0
 

Author Comment

by:htabbach
ID: 26092537
Kurt, the table has no rows as it stands now, why would you want me to run a select statement?
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26092555
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...
0
 

Author Comment

by:htabbach
ID: 26092574
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
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26092642
:) 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'

Open in new window

0
 

Author Comment

by:htabbach
ID: 26092659
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.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26092774
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'
0
 

Author Comment

by:htabbach
ID: 26092780
Yes:

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

Expert Comment

by:HainKurt
ID: 26092809
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
0
 
LVL 61

Expert Comment

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

Author Comment

by:htabbach
ID: 26092820
Kurt,
I am not sure, I am not the DBA, we may be using 2003
0
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 26092870
try something like this...
CREATE TABLE #RowNumber (RowNumber int IDENTITY (1, 1),  invoiceid int);

INSERT #RowNumber (invoiceid)
SELECT InvoiceID   
  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';

INSERT INTO [360_InvoiceTemp]   
      (InvoiceID, ClientID, CompanyID, SiteID, InvoiceDate, QB_InvoiceNumber,   
       InvoiceTotal, InvoiceTax, Comments, UserID, Name)   
SELECT InvoiceID, ClientID, CompanyID, SiteID, InvoiceDate,  
       r.RowNumber + @QB_InvoiceNumber, 
       InvoiceTotal, InvoiceTax, Comments, @UserID, Name   
  FROM [360_Invoice], #RowNumber r
 WHERE r.invoiceid=[360_Invoice].InvoiceID
   AND [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'

Open in new window

0
 
LVL 7

Expert Comment

by:aplusexpert
ID: 26095370
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
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based developeā€¦
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recovā€¦
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question