Using SQL Functions inside Insert Statements

Question

I have an invoice table in my database into which new invoices are inserted. Recently we have upgraded our services and have many users inserting invoices into the table. I have written a function that calculates the next invoice number in the sequence (the sequence isn't just sequential numbering).

MAX(CAST(SUBSTRING([InvoiceNumber], 3, LEN([InvoiceNumber]) - 2) AS INT)) FROM [Invoice] WHERE [InvoiceNumber] LIKE @Prefix + '%'

I now want to use this function in an insert statement as follows

INSERT INTO [Invoice]
([InvoicedTo]
 ,[InvoiceNumber]
 ,[InvoiceDate]
 ,[Description]
 ,[Amount])
VALUES
(@InvoicedTo
 ,NextInvoiceNumber(@Prefix)
 ,@InvoiceDate
 ,@Description
 ,@Amount)

What I want to make sure that using this insert statement will guarantee that the invoice number cannot be duplicated when multiple users are generating invoices. If it can result in duplicates, I would appreciate suggestions on how to stop any duplicates occuring.

Many Thanks, Paul

workabyte · Accepted Answer · 2014-05-09 14:50:10Z

1

dont know if this is helpful

begin tran
declare @nextInvoiceNumber int = dbo.NextInvoiceNumber(@Prefix)

INSERT INTO [Invoice]
([InvoicedTo]
 ,[InvoiceNumber]
 ,[InvoiceDate]
 ,[Description]
 ,[Amount])
VALUES
(@InvoicedTo
 ,@nextInvoiceNumber
 ,@InvoiceDate
 ,@Description
 ,@Amount)

commit

another thing you could/should do is add a constraint to the table that requires it to be unique. then add error handling in the event this does happen that will just get you another number.

answered May 9, 2014 at 14:50

workabyte

3,8152 gold badges33 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

podiluska · Accepted Answer · 2014-05-09 14:44:19Z

0

No, it doesn't guarantee uniqueness.

You should wrap the call inside a serializable transaction.

answered May 9, 2014 at 14:44

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Comments

Vasan · Accepted Answer · 2014-05-09 14:46:23Z

0

You need to embed the insert script with in a transaction (Begin and Commit/Rollback) with appropriate isolation level.

answered May 9, 2014 at 14:46

Vasan

3751 silver badge12 bronze badges

Collectives™ on Stack Overflow

Using SQL Functions inside Insert Statements

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related