Home Use of 'sys.sp_sequence_get_range' in Table Value Function
Reply: 0

Use of 'sys.sp_sequence_get_range' in Table Value Function

user3350
1#
user3350 Published in July 17, 2018, 7:55 am

Situation/Goal:

I have 3 integer sequences from 1 to [INT_MAX] (SEQ_A, SEQ_B, SEQ_C) and I wanted to write a table value function to get a range of numbers from either sequence.

This is the Code I came up with:

CREATE FUNCTION [dbo].[GetSequenceNumber]
(
    @TabelField VARCHAR(256),
    @Range INT
)
RETURNS @RETVALTABLE TABLE
(
    SEQUENCE_NUMBER INT
)
AS
BEGIN

    IF(ISNULL(@Range, 0) <= 0 OR @TabelField IS NULL)
        RETURN;

    DECLARE @FirstSeqNum sql_variant;
    DECLARE @LastSeqNum sql_variant;
    DECLARE @FirstSequenceNumber as INT;
    DECLARE @LastSequenceNumber as INT;

    IF @TabelField = 'FIELD_A' BEGIN
        EXEC sys.sp_sequence_get_range N'SEQ_A', @Range, @FirstSeqNum OUTPUT, @LastSeqNum OUTPUT;  
        SET @FirstSequenceNumber = CONVERT(INT, @FirstSeqNum);
        SET @LastSequenceNumber = CONVERT(INT, @LastSeqNum);

        WHILE @FirstSequenceNumber <= @LastSequenceNumber BEGIN
            INSERT INTO @RETVALTABLE SELECT @FirstSequenceNumber;
            SET @FirstSequenceNumber += 1;
        END
    END

    ELSE IF @TabelField = 'FIELD_B' BEGIN
        EXEC sys.sp_sequence_get_range N'SEQ_B', @Range, @FirstSeqNum OUTPUT, @LastSeqNum OUTPUT;  
        SET @FirstSequenceNumber = CONVERT(INT, @FirstSeqNum);
        SET @LastSequenceNumber = CONVERT(INT, @LastSeqNum);

        WHILE @FirstSequenceNumber <= @LastSequenceNumber BEGIN
            INSERT INTO @RETVALTABLE SELECT @FirstSequenceNumber;
            SET @FirstSequenceNumber += 1;
        END
    END

    ELSE IF @TabelField = 'FIELD_C' BEGIN
        EXEC sys.sp_sequence_get_range N'SEQ_C', @Range, @FirstSeqNum OUTPUT, @LastSeqNum OUTPUT;  
        SET @FirstSequenceNumber = CONVERT(INT, @FirstSeqNum);
        SET @LastSequenceNumber = CONVERT(INT, @LastSeqNum);

        WHILE @FirstSequenceNumber <= @LastSequenceNumber BEGIN
            INSERT INTO @RETVALTABLE SELECT @FirstSequenceNumber;
            SET @FirstSequenceNumber += 1;
        END
    END

    RETURN
END

Why am I doing this:

  • I want all the numbers from a specific range, preferably as a table (unlike sys.sp_sequence_get_range which just gives you the first and the last number)
  • I do not care about the name of the sequences, I just "tell" the table function for which field I want numbers
  • I could do some additional checks before inserting the sequence number in @RETVALTABLE (and skipe some numbers for example).

Problem:

I can create the function, I can alter it, but I can not execute it. When I am calling the function I always get the error: "Only functions and certain extended stored procedures can be executed within a function."

Well, the error is very self explaining and the problem probably is the EXEC sys.sp_sequence_get_range N'SEQ_X', @Range, @FirstSeqNum OUTPUT, @LastSeqNum OUTPUT; part.

Question:

How do I get rid of the error? Is there a better way to achieve the goal than with a table value function?

Top priority here is to get all the numbers from a certain range from a certain sequence (and not just the start and end point).

(Solution should be available for Microsoft SQL Server 2012 and higher)

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.391682 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO