Home Use of 'sys.sp_sequence_get_range' in Table Value Function
 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)