Saturday 27 June 2009

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

6 comments:

Shep said...

This is one of the few examples one finds on the internet which is truly applicable to real-world scenarios. I have used this example as a foundation in a database. I generate purchase order and service order numbers with it. I have built upon the example to include Prefix and Suffix columns in the IDGenerator table, which gives admins the flexibility to give their number sequences some form of organization and readability.

Excellent work, Sam.

Shep said...

Correction:

The Prefix and Suffix columns are not used in the IDGenerator table.

I have modified the stored procedure to incorporate any prefix or suffix stored in the IDParameter table.

Shep said...

Correction:

The Prefix and Suffix columns are not used in the IDGenerator table.

I have modified the stored procedure to incorporate any prefix or suffix stored in the IDParameter table.

Samsudeen B said...

Thanks shep...
Your comments are very encouraging

Unknown said...

north face, hogan, coach outlet, air force, hollister pas cher, michael kors, coach outlet, lululemon, burberry, sac guess, nike roshe, nike free, true religion jeans, air jordan pas cher, lacoste pas cher, ralph lauren pas cher, michael kors, kate spade outlet, ralph lauren uk, michael kors, new balance pas cher, true religion jeans, ray ban uk, nike roshe run, converse pas cher, michael kors, hermes, mulberry, vans pas cher, louboutin pas cher, true religion outlet, ray ban pas cher, nike air max, oakley pas cher, air max, timberland, nike free run uk, kate spade handbags, nike air max, hollister, tn pas cher, replica handbags, nike air max, vanessa bruno, abercrombie and fitch, sac longchamp, true religion jeans, nike blazer, coach purses, longchamp pas cher, north face

theteeski said...

replica bags online s79 m2t63p9p87 bags replica ysl e22 f7m09h1k17 high quality designer replica h57 e5t38u6b40