AUTO GENERATE AUTO INCREMENTED ALPHANUMERIC SEQUENTIAL CODE IN SQL SERVER

Description: While working on project I got the requirement to auto generate sequential alphanumeric code having specified length and prefix. So I created a user defined function to auto generate code and called this function from the stored procedure to get and save item code with other details.

Implementation: Let’s create a table, user defined function and stored procedure to understand.

Create a table 'tbItemMaster' using following script

CREATE TABLE tbItemMaster

(

            ItemId             INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

            ItemName       VARCHAR(100),

            ItemCode        VARCHAR(15),

            ItemPrice         DECIMAL(10,2),

            Quantity          INT

)

Create a user defined function 'fnItemCode' to get new item code of specified length and specified prefix.

GO

CREATE FUNCTION fnItemCode

(

            @CodePrefix VARCHAR(10),

            @CodeLength INT

)

RETURNS VARCHAR(20)

AS

BEGIN

--Get maximum item id from table i.e. the last generated itemid in the table. (initially 0 if table has no data).

DECLARE @MaxItemId INT;

SET @MaxItemId= ISNULL((SELECT MAX(ItemId) FROM tbItemMaster),0)

--Increment maxitemid by 1 to get next item id.

SET @MaxItemId+=1

DECLARE @ItemCode VARCHAR(20),@i INT=1;

WHILE(@i=1)

BEGIN

--Generate new item code of specified code length prefixed by specified prefix passed as parameters.

SET @ItemCode=@CodePrefix +RIGHT(REPLICATE('0', @CodeLength-1) +CONVERT(VARCHAR(20),@MaxItemId),@CodeLength)

--Check generated item code. If already exists then get next item code untill we get fresh item code.

IF EXISTS(SELECT 1 FROM tbItemMaster WHERE ItemCode=@ItemCode)

BEGIN

            SET @MaxItemId +=1

END

ELSE

BEGIN

            SET @i=0

END

END

--Return newly generated item code

RETURN @ItemCode

END

Now In ‘spItemDetails_Save’ stored procedure we just need to call above created function to get auto generated alphanumeric item code to save in table with other item details as:

GO

CREATE PROC spItemDetails_Save

(

            @ItemName   VARCHAR(100),         

            @ItemPrice     DECIMAL(10,2),

            @Quantity      INT

)

AS

BEGIN

            SET NOCOUNT ON;

            --Get New Item Code by calling our function.

            DECLARE @ItemCode VARCHAR(20)=(SELECT dbo.fnItemCode('EMP-',5))

            --Insert item detail including generated item code in table

            INSERT INTO tbItemMaster (ItemName, ItemCode, ItemPrice, Quantity)

            VALUES(@ItemName,@ItemCode,@ItemPrice, @Quantity);

END

Now Let's execute stored procedure by passing parameters values

spItemDetails_Save 'Tooth Paste',80,100

Check data in table

SELECT * FROM tbItemMaster

Result:

ItemId	ItemName	ItemCode	ItemPrice	Quantity
1	Tooth Paste	EMP-00001	80.00	100

Let's execute stored procedure again by passing parameters values

spItemDetails_Save 'Bath Soap',80,70

Check data in table

SELECT * FROM tbItemMaster

Result:

ItemId	ItemName	ItemCode	ItemPrice	Quantity
1	Tooth Paste	EMP-00001	80.00	100
2	Bath Soap	EMP-00002	80.00	70

Let's execute stored procedure again by passing parameters values

spItemDetails_Save 'Hair Oil',150,20

Check data in table

SELECT * FROM tbItemMaster

Result:

ItemId	ItemName	ItemCode	ItemPrice	Quantity
1	Tooth Paste	EMP-00001	80.00	100
2	Bath Soap	EMP-00002	80.00	70
3	Hair Oil	EMP-00003	150.00	20