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 recomments 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.