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.
