SQL Server Portal

sp_sequence_get_range System stored procedure is shipped with SQL SERVER 2012 and it is one of the handy system stored procedures when it comes to fetch range of IDs from SQL Server.
Note : Sequence is a prerequisite for this article.

Syntax :

sp_sequence_get_range [ @sequence_name = ] N''
, [ @range_size = ] range_size
, [ @range_first_value = ] range_first_value OUTPUT
[, [ @range_last_value = ] range_last_value OUTPUT ]
[, [ @range_cycle_count = ] range_cycle_count OUTPUT ]
[, [ @sequence_increment = ] sequence_increment OUTPUT ]
[, [ @sequence_min_value = ] sequence_min_value OUTPUT ]
[, [ @sequence_max_value = ] sequence_max_value OUTPUT ]
[ ; ]

Explanation :
Lets proceed step by step :

Step 1:

Lets create a sequence.

CREATE SEQUENCE [dbo].[Invoice]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000
CYCLE
CACHE
GO

Step 2:

Lets pick up a new ID from this sequence.

SELECT NEXT VALUE FOR dbo.[Invoice] as [New ID]
--OUTPUT

New ID
1

(1 row(s) affected)

Step 3:

Lets fetch the new IDs via sp_sequence_get_range and see the difference.

Example:

DECLARE @range_first_value sql_variant
, @range_first_value_output sql_variant
, @range_last_value_output sql_variant

EXEC sp_sequence_get_range
@sequence_name = N'dbo.Invoice'
, @range_size = 4
, @range_first_value = @range_first_value_output OUTPUT
, @range_last_value = @range_last_value_output OUTPUT

SELECT @range_first_value_output AS [First ID]
,@range_last_value_output as [Last ID]
--OUTPUT

First ID       Last ID
2                5

(1 row(s) affected)

In the above example, you can see that this stored procedure returns a range from 2-5 IDs (First Value – Last Value),  so can use all four IDs (2, 3,4, 5) in your application.

If you execute the same script, 2nd time it will give you the range from 6-9 IDs and it will carry on till it reaches its maximum ID.

sp_sequence_get_range is not limited to return First & Last ID only. It will return other info related to sequence as well.

Step 4:

In Step 2 & Step 3, you can see both techniques are used to get new ID (s), but the difference between both of them is the requirement.

Whenever you need only one new ID you need to use NEXT VALUE & and if you need to get a range of new IDs from sequence you need to use sp_sequence_get_range.

Step 5:
Lets create another example to return all possible result sets from sp_sequence_get_range.

Example:

DECLARE @First_Value sql_variant
, @Last_Value sql_variant
, @Cycle_Count int
, @Sequence_Increament sql_variant
, @Sequence_Min_Value sql_variant
, @Sequence_Max_Value sql_variant ;

EXEC sys.sp_sequence_get_range
@sequence_name = N'dbo.Invoice'
, @range_size = 5
, @range_first_value = @First_value OUTPUT
, @range_last_value = @Last_Value OUTPUT
, @range_cycle_count = @Cycle_Count OUTPUT
, @sequence_increment = @Sequence_Increament OUTPUT
, @sequence_min_value = @Sequence_Min_Value OUTPUT
, @sequence_max_value = @Sequence_Max_Value OUTPUT ;

SELECT
@First_value AS FirstVal
, @Last_Value AS LastVal
, @Cycle_Count AS CycleCount
, @Sequence_Increament AS SeqIncrement
, @Sequence_Min_Value AS MinSeq
, @Sequence_Max_Value AS MaxSeq ;

Real world example :

I recently developed a billing system for one of my clients and the major requirement was to open 4 different invoices before saving the previous invoices.

And the problem was that I could not get a new ID without saving the previous invoice. So, I used this technique and it was successful.

Reference : MSDN

Read Full Post »