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

Use of 'sys.sp_sequence_get_range' in Table Value Function

InD
1#
InD Published in 2017-12-06 09:02:25Z

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)

h0ffm4nn
2#
h0ffm4nn Reply to 2017-12-06 13:49:11Z

Without getting into the call to the sproc (which MSSQL started yelling about immediately and didn't stop (OUTPUT keyword, calling proc inside function, something), I think I've got you here.

This code will return you the values in the range without actually incrementing the sequence(though the method is iterative rather than set-based. I'm going to try to set-base it now):

CREATE FUNCTION dbo.GetSeqRange(@seqname VARCHAR(MAX), @start INT, @end INT)
    RETURNS @ReturnTable TABLE (
        ReturnValue INT
    )
AS
BEGIN
DECLARE @increment INT = (SELECT CONVERT(INT, increment) FROM sys.sequences WHERE [name] = @seqname)
DECLARE @i INT = @start
WHILE @i <= @end
BEGIN
    INSERT @ReturnTable VALUES(@i)
    SET @i += @increment
END
    RETURN
END

To increment the sequence, you'd replace the inside of the WHILE block with SELECT NEXT VALUE FOR <seqname> and insert those into the result table. Let me know if that helps! Or if I misunderstood you then I'll modify.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO