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.
In previous articles i explained How to Auto generate auto incremented unique alphanumeric id or number in sql server and Update table data using inner join in sql server
and CTE recursive query to get employee manager hierarchy with level and Drop or truncate parent table by dropping all foreign key constraints and Concatenate rows values as a comma separated string using for xml path and stuff 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.
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..