Auto Generate Auto Incremented Alphanumeric Sequential Code in SQL Server

Introduction: In this article I am going to explain How to automatically generate next unique alphanumeric sequential item code of specified length and prefix using user defined function 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

Now over to you:
A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..