[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL 2005 Function To Increment A Value

Posted on 2007-11-20
14
Medium Priority
?
2,038 Views
Last Modified: 2010-08-05
I have a parameter table in the database which contains only 1 row.  In that tabel I have the next available Invoice Number.  I want to create a function that I can call that will return the next available Invoice Number and increment the value in the table.  My function is as follows

ALTER FUNCTION [BIS].[myfn_GEN_ApplicationParameters_NextInvoiceNbr] ( )
RETURNS INT
AS BEGIN

    DECLARE @LastNbr int
    DECLARE @NextNbr int

    SELECT  @LastNbr = NextAvailableInvoiceNumber
    FROM    dbo.GEN_ApplicationParameter
    WHERE   ( Admin_ApplicationParametersID = 1 )

      --IF @LastNbr IS NULL SET @LastNbr = 0

    SET @NextNbr = ( @LastNbr + 1 )

    UPDATE  [dbo].[GEN_ApplicationParameter]
    SET     NextAvailableInvoiceNumber = @NextNbr
    WHERE   ( Admin_ApplicationParametersID = 1 )
      
    RETURN @LastNbr

   END

When I try to save the function i get the following message:

Msg 443, Level 16, State 15, Procedure myfn_GEN_ApplicationParameters_NextInvoiceNbr, Line 17
Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.

What do I need to do to correct this function?

Thanks In Advance
0
Comment
Question by:KimberleyY
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20325035
Updating of tables is not allowed inside a function, so better modify the function as a procedure
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20325043
or something like do will do the same


ALTER FUNCTION [BIS].[myfn_GEN_ApplicationParameters_NextInvoiceNbr] ( )
RETURNS INT
AS BEGIN

    DECLARE @LastNbr int
    DECLARE @NextNbr int

    SELECT  @LastNbr = NextAvailableInvoiceNumber
    FROM    dbo.GEN_ApplicationParameter
    WHERE   ( Admin_ApplicationParametersID = 1 )

      --IF @LastNbr IS NULL SET @LastNbr = 0

    SET @NextNbr = ( @LastNbr + 1 )

     
    RETURN @LastNbr

   END
   
   GO
   
    UPDATE  [dbo].[GEN_ApplicationParameter]
    SET     NextAvailableInvoiceNumber = [BIS].[myfn_GEN_ApplicationParameters_NextInvoiceNbr] () -1
    WHERE   ( Admin_ApplicationParametersID = 1 )
0
 
LVL 17

Expert Comment

by:HuyBD
ID: 20325059
why dont you use store procedure to update data?
the function can not be use to update or delete data of table
http://www.databasejournal.com/features/mssql/article.php/3348181
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:KimberleyY
ID: 20325116
aneeshattingal: and HuyBD,

I want to retreive the number so I can use it in another table.  When I tried

ALTER FUNCTION [BIS].[myfn_GEN_ApplicationParameters_NextInvoiceNbr] ( )
RETURNS INT
AS BEGIN

    DECLARE @InvoiceNbr INT

    EXECUTE @InvoiceNbr = [BISInfo].[BIS].[mysp_GEN_ApplicationParameters_Update_NextInvoiceNbr]

    RETURN @InvoiceNbr

   END

I buil the function and if I execute it - I get the correct answer - But when I tried to call the function from within another SP I get the following Message

Msg 557, Level 16, State 2, Procedure mysp_Acctg_Invoice, Line 58
Only functions and extended stored procedures can be executed from within a function.


0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20325130
as the error message shows, you can't execute an sp directly inside a function, and the indiect method is not at all recommended because of security
0
 

Author Comment

by:KimberleyY
ID: 20325143
OK - so If in 1 stored procedure I am going to create many invoices how do I get each invoice to have an invoice number - my sql for creating the SP  is

UPDATE  Acctg_AccountingDocument
        SET     [Acctg_AccountingDocument].[AccountingDocument_ReferenceNbr] = BIS.myfn_GEN_ApplicationParameters_NextInvoiceNbr(),
                [Acctg_AccountingDocument].[AccountingDocument_Date] = GETDATE(),
                [Acctg_AccountingDocument].[AccountingDocument_DateDue] = DATEADD(d, 30, GETDATE()),
                [Acctg_AccountingDocument].[AccountingDocument_TotalAmount] = BIS.myvw_Acctg_AccountingDocumentDetail_Summed.TotalAmount,
                [Acctg_AccountingDocument].[AccountingDocument_TotalAmount_Cust] = BIS.myvw_Acctg_AccountingDocumentDetail_Summed.TotalAmount_Cust,
                [Acctg_AccountingDocument].[AccountingDocument_TotalAmount_Grant] = BIS.myvw_Acctg_AccountingDocumentDetail_Summed.TotalAmount_Grant,
                [Acctg_AccountingDocument].[AccountingDocumentStatusID] = 2,
                [Acctg_AccountingDocument].[AccountingDocumentStatusDate] = GETDATE(),
                [Acctg_AccountingDocument].[AccountingDocumentStatusSetBy] = @Proc_Name,
                        [ModifiedBy] = @Proc_Name,
                        [ModifiedOn] = GetDate()

        FROM     dbo.Acctg_AccountingDocument
                  INNER JOIN BIS.myvw_Acctg_AccountingDocumentDetail_Summed
                    ON dbo.Acctg_AccountingDocument.AccountingDocumentID = BIS.myvw_Acctg_AccountingDocumentDetail_Summed.AccountingDocumentID
               
        WHERE    ( dbo.Acctg_AccountingDocument.AccountingDocumentID
                              IN (Select #BIS_tmp_Array.intID from #BIS_tmp_Array)
                          )

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20325225
Why can't you combine both of these to a single sp
0
 

Author Comment

by:KimberleyY
ID: 20325255
brecasue I am doing multiple row updates in a single call
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20325266
did u check my  post with the ID:20325043
0
 

Author Comment

by:KimberleyY
ID: 20325338
how do I check by the ID?

I did find this code which does the trick

declare @intCounter int
set @intCounter = 0
update Yaks
SET @intCounter = YakSequenceNumber = @intCounter + 1

I can then update the paramter tabel at the end with the final value.  this only works if 1 perosn is doing this at a time so i would like to see your post
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20325509
this should work
declare @Proc_Name int
 
BEGIN TRAN
UPDATE  Acctg_AccountingDocument
        SET     [Acctg_AccountingDocument].[AccountingDocument_ReferenceNbr] =
			row_number() over (order by AccountingDocumentID) +
			isnull((SELECT NextAvailableInvoiceNumber FROM dbo.GEN_ApplicationParameter WHERE ( Admin_ApplicationParametersID = 1 )), 0),
                [Acctg_AccountingDocument].[AccountingDocument_Date] = GETDATE(),
                [Acctg_AccountingDocument].[AccountingDocument_DateDue] = DATEADD(d, 30, GETDATE()),
                [Acctg_AccountingDocument].[AccountingDocument_TotalAmount] = BIS.myvw_Acctg_AccountingDocumentDetail_Summed.TotalAmount,
                [Acctg_AccountingDocument].[AccountingDocument_TotalAmount_Cust] = BIS.myvw_Acctg_AccountingDocumentDetail_Summed.TotalAmount_Cust,
                [Acctg_AccountingDocument].[AccountingDocument_TotalAmount_Grant] = BIS.myvw_Acctg_AccountingDocumentDetail_Summed.TotalAmount_Grant,
                [Acctg_AccountingDocument].[AccountingDocumentStatusID] = 2,
                [Acctg_AccountingDocument].[AccountingDocumentStatusDate] = GETDATE(),
                [Acctg_AccountingDocument].[AccountingDocumentStatusSetBy] = @Proc_Name,
                        [ModifiedBy] = @Proc_Name,
                        [ModifiedOn] = GetDate()
        FROM     dbo.Acctg_AccountingDocument
                  INNER JOIN BIS.myvw_Acctg_AccountingDocumentDetail_Summed
                    ON dbo.Acctg_AccountingDocument.AccountingDocumentID = BIS.myvw_Acctg_AccountingDocumentDetail_Summed.AccountingDocumentID
               
        WHERE    ( dbo.Acctg_AccountingDocument.AccountingDocumentID
                              IN (Select #BIS_tmp_Array.intID from #BIS_tmp_Array)
                          )
 
	-- update the number back
	UPDATE  [dbo].[GEN_ApplicationParameter]
	    SET     NextAvailableInvoiceNumber = NextAvailableInvoiceNumber + @@rowcount  -- how many we used
	    WHERE   ( Admin_ApplicationParametersID = 1 ) 
COMMIT TRAN

Open in new window

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20325515
forget me. please ignore that comment
0
 
LVL 25

Accepted Solution

by:
imitchie earned 2000 total points
ID: 20325537
back for more. this one should work (row number stuff moved to select. can't use with update)
BEGIN TRAN
UPDATE  Acctg_AccountingDocument
        SET     [Acctg_AccountingDocument].[AccountingDocument_ReferenceNbr] =
			rw +
			isnull((SELECT NextAvailableInvoiceNumber FROM dbo.GEN_ApplicationParameter WHERE ( Admin_ApplicationParametersID = 1 )), 0),
                [Acctg_AccountingDocument].[AccountingDocument_Date] = GETDATE(),
                [Acctg_AccountingDocument].[AccountingDocument_DateDue] = DATEADD(d, 30, GETDATE()),
                [Acctg_AccountingDocument].[AccountingDocument_TotalAmount] = BIS.myvw_Acctg_AccountingDocumentDetail_Summed.TotalAmount,
                [Acctg_AccountingDocument].[AccountingDocument_TotalAmount_Cust] = BIS.myvw_Acctg_AccountingDocumentDetail_Summed.TotalAmount_Cust,
                [Acctg_AccountingDocument].[AccountingDocument_TotalAmount_Grant] = BIS.myvw_Acctg_AccountingDocumentDetail_Summed.TotalAmount_Grant,
                [Acctg_AccountingDocument].[AccountingDocumentStatusID] = 2,
                [Acctg_AccountingDocument].[AccountingDocumentStatusDate] = GETDATE(),
                [Acctg_AccountingDocument].[AccountingDocumentStatusSetBy] = @Proc_Name,
                        [ModifiedBy] = @Proc_Name,
                        [ModifiedOn] = GetDate()
        FROM     (select row_number() over (order by AccountingDocumentID) as rw, AccountingDocumentID
		  from dbo.Acctg_AccountingDocument
                  INNER JOIN BIS.myvw_Acctg_AccountingDocumentDetail_Summed
                    ON dbo.Acctg_AccountingDocument.AccountingDocumentID = BIS.myvw_Acctg_AccountingDocumentDetail_Summed.AccountingDocumentID
		  WHERE  ( dbo.Acctg_AccountingDocument.AccountingDocumentID IN (Select #BIS_tmp_Array.intID from #BIS_tmp_Array) )
		 ) keys
		  INNER JOIN dbo.Acctg_AccountingDocument on KEYS.AccountingDocumentID = dbo.Acctg_AccountingDocument.AccountingDocumentID
                  INNER JOIN BIS.myvw_Acctg_AccountingDocumentDetail_Summed
                    ON KEYS.AccountingDocumentID = BIS.myvw_Acctg_AccountingDocumentDetail_Summed.AccountingDocumentID
 
	-- update the number back
	UPDATE  [dbo].[GEN_ApplicationParameter]
	    SET     NextAvailableInvoiceNumber = NextAvailableInvoiceNumber + @@rowcount  -- how many we used
	    WHERE   ( Admin_ApplicationParametersID = 1 ) 
COMMIT TRAN

Open in new window

0
 

Author Closing Comment

by:KimberleyY
ID: 31410280
Thanks!  Nice job!

How do you get the code in the window with the numbers?
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

650 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