CODEWITHSUNDEEP
sqlsql-server
Byron Scott
Byron Scott

Reputation: 63

How to create value to be inserted based on identity in SQL Server?

This is how I do it on my stored procedure:

SET @ProductCode = CONCAT('P',RIGHT ('0000'+ CAST (IDENT_CURRENT('[dbo].[Products]') + 1 AS varchar), 5))

This problem with this is when inserting the first record.

It starts with 2

Actual result:

ProductId ProductCode
1 P00002
2 P00003
3 P00004
4 P00005

Expected result:

ProductId ProductCode
1 P00001
2 P00002
3 P00003
4 P00004

Upvotes: 1

Views: 68

Answers (3)

Pradeep Kumar
Pradeep Kumar

Reputation: 6979

With your current table structure, you should declare a table variable. Then use the OUTPUT INTO clause to get the newly inserted values. This will work irrespective whether your ProductId column is identity or not.

declare @Output table (ProductId bigint, ProductCode varchar(255))

insert into Products (FieldA, FieldB)
output inserted.ProductId, 'P' + Format(inserted.ProductId, '0000') into @Output
values ('Test A1', 'Test B1')
      ,('Test A2', 'Test B2')
      ,('Test A3', 'Test B3')

select * from @Output

result

If it is possible to modify your table structure, I would suggest adding a computed column to avoid all these hassles.

alter table Products
add ProductCode as 'P' + Format(ProductId, '0000')

This would create a computed column which you can query as any other regular column.

computed column

Upvotes: 1

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Please use below query (I have removed (+1) )

    SET @ProductCode = CONCAT('P',RIGHT ('0000'+ CAST (IDENT_CURRENT('[dbo].[Products]')  AS varchar), 5))

But I would recommend to add Product_Code as computed column;

create table products (ProductId int,ProductCode as CONCAT('P',RIGHT ('0000'+ cast( productid  as varchar(4)), 5))  )

insert into products values(1);
insert into products values(2);
insert into products values(3);

select * from products

Output:

ProductId ProductCode
1 P00001
2 P00002
3 P00003

db<>fiddle here

Upvotes: 0

marc_s
marc_s

Reputation: 754518

Why not just use the ProductId that you already have??

SET @ProductCode = CONCAT('P', RIGHT('0000'+ CAST(ProductId AS VARCHAR(5)), 5))

Or as @Larnu suggested in a comment - use a computed column in your table with this logic:

ALTER TABLE dbo.YourTable
    ADD ProductCode AS CONCAT('P', RIGHT('0000'+ CAST(ProductId AS VARCHAR(5)), 5)) PERSISTED;

and be done with it. Every time a row is inserted, your column ProductCode is computed and shows the expected values.

Upvotes: 0

Related Questions