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.
