Dibaca dalam bahasa Inggeris Edit

Kongsi melalui

sp_sequence_get_range (Transact-SQL)

  • Artikel

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

Returns a range of sequence values from a sequence object. The sequence object generates and issues the number of values requested and provides the application with metadata related to the range.

For a more information about sequence numbers, see Sequence Numbers.

Transact-SQL syntax conventions

Syntax

syntaxsql 
sp_sequence_get_range
    [ @sequence_name = ] N'sequence_name'
    , [ @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 ]
[ ; ]

Arguments

[ @sequence_name = ] N'sequence_name'

The name of the sequence object. The schema is optional. @sequence_name is nvarchar(776), with no default.

[ @range_size = ] range_size

The number of values to fetch from the sequence. @range_size is bigint, with no default.

[ @range_first_value = ] range_first_value OUTPUT

Output parameter returns the first (minimum or maximum) value of the sequence object used to calculate the requested range. @range_first_value is an OUTPUT parameter of type sql_variant, with the same base type as the sequence object used in the request.

[ @range_last_value = ] range_last_value OUTPUT

Optional output parameter returns the last value of the requested range. @range_last_value is an OUTPUT parameter of type sql_variant, with the same base type as the sequence object used in the request.

[ @range_cycle_count = ] range_cycle_count OUTPUT

Optional output parameter returns the number of times that the sequence object cycled in order to return the requested range. @range_cycle_count is an OUTPUT parameter of type int.

[ @sequence_increment = ] sequence_increment OUTPUT

Optional output parameter returns the increment of the sequence object used to calculate the requested range. @sequence_increment is an OUTPUT parameter of type sql_variant, with the same base type as the sequence object used in the request.

[ @sequence_min_value = ] sequence_min_value OUTPUT

Optional output parameter returns the minimum value of the sequence object. @sequence_min_value is an OUTPUT parameter of type sql_variant, with the same base type as the sequence object used in the request.

[ @sequence_max_value = ] sequence_max_value OUTPUT

Optional output parameter returns the maximum value of the sequence object. @sequence_max_value is an OUTPUT parameter of type sql_variant, with the same base type as the sequence object used in the request.

Return code values

0 (success) or 1 (failure).

Remarks

sp_sequence_get_range is in the sys schema, and can be referenced as sys.sp_sequence_get_range.

Cycling sequences

If necessary, the sequence object cycles the appropriate number of times to service the requested range. The number of times cycled is returned to the caller through the @range_cycle_count parameter.

Nota

When cycling, a sequence object restarts from the minimum value for an ascending sequence and the maximum value for a descending sequence, not from the start value of the sequence object.

Non-cycling sequences

If the number of values in the requested range is greater than the remaining available values in the sequence object, the requested range isn't deducted from the sequence object, and the following error 11732 is returned:

The requested range for sequence object '%.*ls' exceeds the maximum or minimum limit. Retry with a smaller range.

Permissions

Requires UPDATE permission on the sequence object or the schema of the sequence object.

Examples

The following examples use a sequence object named Test.RangeSeq. Use the following statement to create the Test.RangeSeq sequence.

SQL 
CREATE SCHEMA Test;
GO

CREATE SEQUENCE Test.RangeSeq AS INT START
    WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 25
    CYCLE CACHE 10;

A. Retrieve a range of sequence values

The following statement gets four sequence numbers from the Test.RangeSeq sequence object and returns the first of the numbers to the user.

SQL 
DECLARE @range_first_value_output SQL_VARIANT;

EXEC sys.sp_sequence_get_range @sequence_name = N'Test.RangeSeq',
    @range_size = 4,
    @range_first_value = @range_first_value_output OUTPUT;

SELECT @range_first_value_output AS FirstNumber;

B. Return all output parameters

The following example returns all the output values from the sp_sequence_get_range procedure.

SQL 
DECLARE @FirstSeqNum SQL_VARIANT,
    @LastSeqNum SQL_VARIANT,
    @CycleCount INT,
    @SeqIncr SQL_VARIANT,
    @SeqMinVal SQL_VARIANT,
    @SeqMaxVal SQL_VARIANT;

EXEC sys.sp_sequence_get_range @sequence_name = N'Test.RangeSeq',
    @range_size = 5,
    @range_first_value = @FirstSeqNum OUTPUT,
    @range_last_value = @LastSeqNum OUTPUT,
    @range_cycle_count = @CycleCount OUTPUT,
    @sequence_increment = @SeqIncr OUTPUT,
    @sequence_min_value = @SeqMinVal OUTPUT,
    @sequence_max_value = @SeqMaxVal OUTPUT;

-- The following statement returns the output values
SELECT @FirstSeqNum AS FirstVal,
    @LastSeqNum AS LastVal,
    @CycleCount AS CycleCount,
    @SeqIncr AS SeqIncrement,
    @SeqMinVal AS MinSeq,
    @SeqMaxVal AS MaxSeq;

Changing the @range_size argument to a large number such as 75 causes the sequence object to cycle. Check the @range_cycle_count argument to determine if and how many times the sequence object has cycled.

C. Example using ADO.NET

The following example gets a range from the Test.RangeSeq by using ADO.NET.

C# 
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sys.sp_sequence_get_range";
cmd.Parameters.AddWithValue("@sequence_name", "Test.RangeSeq");
cmd.Parameters.AddWithValue("@range_size", 10);

// Specify an output parameter to retrieve the first value of the generated range.
SqlParameter firstValueInRange = new SqlParameter("@range_first_value", SqlDbType.Variant);
firstValueInRange.Direction = ParameterDirection.Output;
cmd.Parameters.Add(firstValueInRange);

conn.Open();
cmd.ExecuteNonQuery();

// Output the first value of the generated range.
Console.WriteLine(firstValueInRange.Value);

Related content