Simulating Oracle Sequences in T-SQL Code

Sequences are Oracle special database objects designed to produce values in sequence, starting with an initial value and incrementing by a given value. The defaults are to initialize the sequence at 1 and increment by 1. Although SQL Server uses the Identity column to obtain a similar functionality, Oracle sequences have two main advantages over the Identity column:

Listing 1 shows the CreateSequence procedure, which uses T-SQL code to create a sequence. For example, to create a sequence called prod_seq that’s initialized at 1 and incremented by 1, you simply execute the code

exec sp_CreateSequence 'prod_seq' 

Oracle also has two major sequence functions called CurrVal and NextVal. The CurrVal function gives the current value in a sequence, whereas the NextVal function produces the next value in a sequence. I used T-SQL code to implement similar operations. Listing 2 shows the T-SQL version of the CurrVal function. This function runs T-SQL’s IDENT_CURRENT statement, which obtains the current value in the specified sequence. Listing 3 shows a T-SQL stored procedure called NextVal that obtains the name of the target sequence and outputs the next value in that sequence. (NextVal can’t be a function because dynamic T-SQL code executes inside it.)

To call the CurrVal function and NextVal procedure for the prod_seq sequence, you’d use code such as

DECLARE @n int select dbo.CurrVal ('prod_seq')exec NextVal 'prod_seq',@n outputprint @n

— Eli Leiba
[email protected]

Editor’s Note
Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you’ll get $50.