Create customized auto increment Sequence number in SQL Server

Last Reply 23 days ago By bigbear

Posted one month 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 28 days ago Modified on 28 days 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 23 days 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)))