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
Posted 4 months ago Modified on 4 months ago

Hi bigbear,

Create a function and wite below query.

SQL

CREATE TABLE TestUnique(Id VARCHAR(15))
GO
DECLARE @lastId CHAR(9), @lastYear CHAR(9), @curYear NVARCHAR(2)
SET @lastId = (SELECT max(Id) FROM TestUnique) 

SET @lastYear = SUBSTRING(@lastId, 4, 2)

SET @curYear = (SELECT FORMAT(GETDATE(), 'yy'))

IF @lastId is null
BEGIN
SET @lastId = '001'
END

IF @lastYear < @curYear
BEGIN
SET @lastId = '001'
END

INSERT INTO TestUnique VALUES('VSP'+ @curYear +'-'+ right('000' + convert(varchar(10),right(@lastId,3) + 1),3))

SELECT * FROM TestUnique

 

 

 

 


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)))