Auto generate Unique ID (Random ID) with Prefix using Stored Procedure in SQL Server

AnkitPal
 
on Apr 30, 2021 01:49 AM
1147 Views

I want to produce code as - 202503006001

But the result is 2025030061

anyone can correct the above query.?

ALTER PROCEDURE [dbo].[Add_Products]
(
	@CategoryId nchar(10),
	@Category nvarchar(50),
	@SubCategoryId nchar(10),
	@SubCategory nvarchar(50),
	@ProductBrand nvarchar(50),
	@ProductTitle nvarchar(120),
	@ProductSubTitle nvarchar(120),
	@ProductDiscription nvarchar(MAX),
	@ProductImg nvarchar(50),
	@IUserId nvarchar(50),
	@IUserIp nvarchar(50)
)
AS
	BEGIN
	DECLARE @ProductId BIGINT
	DECLARE @ProductCodeIndicator nchar(4)
	DECLARE @ProductCodeSuffix nchar(4)
	DECLARE @ProductCodePrefix nchar(4)
	DECLARE @ProductCodeIndex nchar(4)
	DECLARE @ProductCode nvarchar(50)
	DECLARE @Status nchar(10)
	DECLARE @RecordDateTime datetime
	
	SET NOCOUNT ON;
	SELECT @ProductId=ISNULL(MAX(ProductId),0)+1 from Product_Add	
	--SELECT @BranchCode=SavACPrefix FROM NSBranch WHERE BranchID=@BranchId
	SELECT @ProductCodeSuffix=CategoryId_Suffix FROM Product_Category WHERE SN=@CategoryId			--Get Suffix Value
	SELECT @ProductCodePrefix=SubCategoryId_Suffix FROM Product_SubCategory WHERE SN=@SubCategoryId	--Get Prefix Value
	SELECT @ProductCodeIndex=ISNULL(max(ProductCodeIndex),0)+1 FROM Product_Add WHERE ProductCodeSuffix=@ProductCodeSuffix
    AND ProductCodePrefix=@ProductCodePrefix
    
	SET @ProductId=@ProductId
	SET @ProductCodeIndicator='2025'
	SET @ProductCodeIndex=RIGHT('000' + CAST(@ProductCodeIndex AS VARCHAR(3)),3)
	--I am gettiing error from above line, this query is not fired as my desired this is not replace cast as i am set.
	SET @ProductCode = '2025' + RTRIM(@ProductCodeSuffix) + RTRIM(@ProductCodePrefix) + @ProductCodeIndex
	SET @Status='P'
	SET @RecordDateTime=GETDATE()
	
INSERT INTO Product_Add
	(ProductId, ProductCodeIndicator, ProductCodeSuffix, ProductCodePrefix, ProductCodeIndex, ProductCode, CategoryId, Category,
	SubCategoryId, SubCategory, ProductBrand, ProductTitle,
	ProductSubTitle, ProductDiscription, ProductImg, Status, IUserId, IUserIp, RecordDateTime)
	
VALUES
	(@ProductId, @ProductCodeIndicator, @ProductCodeSuffix, @ProductCodePrefix, @ProductCodeIndex, @ProductCode, @CategoryId,
	@Category, @SubCategoryId, @SubCategory, @ProductBrand, @ProductTitle,
	@ProductSubTitle, @ProductDiscription, @ProductImg, @Status, @IUserId, @IUserIp, @RecordDateTime)

	END

1. ProductCodeIndicator i.e. - 2025 'Fixed'

2. ProductCodeSuffix i.e. - Product Category

3. ProductCodePrefix i.e. - Product Sub Category

and last one that is not giving result as per my requirment

4. ProductCodeIndex i.e. - Autoincreament on the basis of Product Category and Subcategory (that is select from above query, it is increasing the value but not populate with cast.)

I want to produce code as - 202503006001

But the highlited line give below result - 2025030061

where '1' which is placed in last populate such as 001.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Apr 30, 2021 06:38 AM
on May 03, 2021 04:49 AM
AnkitPal says:
SET @ProductCodeIndex=RIGHT('000' + CAST(@ProductCodeIndex AS VARCHAR(3)),3)

Change with below line.

SET @ProductCodeIndex = RIGHT('000' + CAST(CAST(@ProductCodeIndex AS INT) AS VARCHAR(3)),3)

Check with the query.

SQL

DECLARE @ProductCodeIndicator nchar(4) = '2025'
DECLARE @ProductCodeSuffix nchar(2) = '03'
DECLARE @ProductCodePrefix nchar(3) = '006'
DECLARE @ProductCodeIndex nchar(10) = '1'

SET @ProductCodeIndex = RIGHT('000' + CAST(CAST(@ProductCodeIndex AS INT) AS VARCHAR(3)),3)
DECLARE @ProductCode nvarchar(20)
SET @ProductCode = @ProductCodeIndicator + RTRIM(@ProductCodeSuffix) + RTRIM(@ProductCodePrefix) + @ProductCodeIndex
 
SELECT @ProductCode

Output

202503006001