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.