Home Use of 'sys.sp_sequence_get_range' in Table Value Function

# 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#
 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  and insert those into the result table. Let me know if that helps! Or if I misunderstood you then I'll modify.