Create customized auto increment Sequence number in SQL Server

Last Reply 3 months ago By bigbear

Posted 4 months ago

Hello all,

How to create Customized Column with Trigger or Function in T-SQL 

I am working on a database column, I'm deciding between Trigger and Function. I believe 1 of these is what I will need because I don't think Stored Procedure can be ran after an insert.

I'm open to any suggestions though, I have a column that will need a string value then the current year of 2 digits followed by a – and a counter after. On the start of a new year I will need the counter to restart at 001.

How should I do this?

EX. VSP20-001, VSP20-002 … and the next year VSP21-001, VSP21-002 …

Here is the start of my TRIGGER. I know it needs a lot more. I'm not sure if I should do it as a computed column or if I can do it all In just 1 column.

CREATE TRIGGER trg_ProposalTest
ON dbo.Serv_Quotes
AFTER INSERT
AS
BEGIN

DECLARE @yearTst nvarchar(10);
SET @yearTst = (SELECT FORMAT(GETDATE(), 'yy'))

DECLARE @word nvarchar(5);
SET @word = 'VSP';

DECLARE @counter int;
SET @counter = RIGHT(@fullvar,3) + 1; -- still need to create this
You are viewing reply posted by: bigbear 3 months ago.
Posted 3 months ago

This is what I ended up using, I didn't try yours but yours looks good as well. I used a SEQUENCE in SQL Server.

create sequence dbo.ProjID
AS INTEGER
START WITH 10
INCREMENT BY 1;
GO

and to restart it at new year with a job

ALTER SEQUENCE dbo.ProjID
RESTART;

and the column in database using it

testVal nvarchar(10) default ( CONCAT( 'VSP', YEAR( GETDATE() ) % 100 , '-', 
    RIGHT( CONCAT('000', NEXT VALUE FOR dbo.ProjID), 3)))