Create customized auto increment Sequence number in SQL Server

bigbear

SOLVED CLOSED ACTIVE ANSWERED
User: bigbear
Posted: on Mar 09, 2020 12:22 AM
Forum: SQL Server
Answer: 2
Views: 5331

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

Download FREE API for Word, Excel and PDF in ASP.Net

arunkurm...

0 2

Reply
ANSWER
User: arunkurmi
Replied: on Mar 11, 2020 03:43 AM
Modified: on Mar 11, 2020 03:45 AM
Report

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

bigbear

0 3

Reply
ANSWER
User: bigbear
Replied: on Mar 16, 2020 10:48 AM
Report

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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Create customized auto increment Sequence number in SQL Server

Company

Explore

Follow us

Contact Us