Introduction
A sequence object in MS-SQL Server is designated to define and get only integer values, such as int, bigint, smallint, tinyint. However, if we want to generate sequence value(s) that are alpha-numeric, then we can define a Stored Procedure that can combine to generate an alpha-numeric combination of sequence values. This blog gives a complete idea of how this can be implemented.
CREATE DATABASE sampdb1
use sampdb1
--First Create a sequence object s3 which will generate numbers from 1 to 5 and cycles
CREATE SEQUENCE s3
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 5
CYCLE
--Ensuring that the sequence object is defined properly and generating defined values.
SELECT NEXT VALUE FOR s3
--Defining a Stored Procedure that generates a Custom-sequence of values. This stored procedure is going to just get the generated alpha-numeric combination of the sequence.
CREATE PROCEDURE genSeqVals AS
Begin
DECLARE @n as int, @msg varchar(4)
SELECT @n=NEXT VALUE FOR s3
PRINT 'A'+cast(@n as varchar(2))
End
--Execute the following code and check.
Exec genSeqVals
--Defining another Stored Procedure that generates a Custom-sequence of values. This stored procedure returns the alpha-numeric combination of the sequence value that is generated using an OUTPUT parameter.
CREATE PROCEDURE getSeqVals(@res varchar(4) OUTPUT) AS
Begin
DECLARE @n as int, @msg varchar(4)
SELECT @n=NEXT VALUE FOR s3
SET @res = 'A'+cast(@n as varchar(2))
End
--Execute the following code and check.
DECLARE @seqnum varchar(4)
EXECUTE getSeqVals @seqnum output
print @seqnum
This is one way in which sequence objects can be custom-implemented as per the requirement. I hope that the above lines of code have given you deeper insight into T-SQL for custom implementation. Happy coding!