Concurent Invoice Number with SQL Server Store Procedure

There might have many way to manage running number as invoice or other sequence number generating but I would like to bring two among them.

With SQL Server, I would like to show you about what I have been implemented in my previous application in generating invoice running number.

1. Using table to store last running number, store procedure and transaction

We firstly need to create a running number storage table

Create table tblInvoice with fields
InvoiceNumber (int, not null, primary key)
InvoiceType (char(3)).

Then create store procedure which is used to manage its value

PROCEDURE [dbo].[UDP_GETINVOICENUMBER]
@invoice_type as char(3),
@invoice_number AS BIGINT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
UPDATE tblInvoice
SET @invoice_number=InvoiceNumber=InvoiceNumber+1
WHERE(InvoiceType=@invoice_type)
COMMIT TRANSACTION
END

2. Using Identity field
This mean that we need to use the function SCOPE_IDENTITY() function to get the recent added row.

Hope these would give you as references in case of your need. I am sure and some people does not recommends me to use these method either but I have found no other possibility to solve my running number problem beside these above solution.

If you have had another better solution, please let me know so that I could also improve my current system to avoid duplicate number.

Leave a Reply

Your email address will not be published. Required fields are marked *