How to generate Sequence number in SQL Server

Sequence number generation is one of the common requirements in all the OLTP applications.SQL Server supports many ways to generate Sequence numbers. The below example explains how to generate multiple sequences dynamically using the SQL Server

Schema Design

This table will hold the configuration parameters for each of the Sequence Types (eg: PurchaseOrder, GRN etc).The column “SequenceName” column will have the unique code for each Sequence type and the “TableName” column is used to map the name of the “IDGenerator” table. (I.e. multiple IDGenerator tables can be created with the same structure and mapped accordingly)

ID Generation:
The below stored procedure pGetSEQID is used to generate the new sequence number .It accepts the table name as input and return the new sequence number. Since the IDGenerator table can be different for each ID type the stored procedure is written as dynamic.

CREATE PROCEDURE pGetSEQID (
@P_SEQTableName VARCHAR(30)
)
AS
BEGIN
DECLARE @V_SEQValue BIGINT
DECLARE @V_SQLString NVARCHAR(200)
DECLARE @V_ParmDefinition NVARCHAR(200)
DECLARE @V_SEQTableName VARCHAR(30)
SET @V_ParmDefinition = N'@V_SEQValue BIGINT OUTPUT'
SET @V_SQLString = N'INSERT INTO ' +
@P_SEQTableName +
N'(Status) values (''Y'') SELECT @V_SEQValue = SCOPE_IDENTITY()'
exec sp_executesql @V_SQLString,@V_ParmDefinition ,@V_SEQValue =@V_SEQValue OUTPUT
SELECT @V_SEQValue NewSequenceValue
END

EXEC pGetSEQID ‘PurchaseOrder’ will generate the Sequence Number for ID type purchase order

We can make use of Prefix and Suffix columns in the IDParamter table to generate the Sequence number with the required format
Eg: “BL0001” , BL100/0908