Create customized auto increment Sequence number in SQL Server

Last Reply one year ago By bigbear

Posted one year 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: arunkurmi one year ago.
Posted one year ago Modified on one year 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