LISTING 2: Optimized and Simplified Version of the Sequence Generator /* -- This code uses recursive calls of fn_p() to generate a sequence. -- The function can return sequences from -N to N in one call. -- The code is simplified and reduced in size. */ IF EXISTS(SELECT 1 FROM sysobjects WHERE id = object_id('fn_p')) DROP function fn_p GO -- This function generates numbers between 0 and 10^(@exp+1)-1. CREATE function fn_p ( @exp int ) RETURNS @result table (value int) AS BEGIN DECLARE @pow int DECLARE @t table(value int) IF @exp > 9 RETURN -- This line sets the limit of the int data type. SET @pow = power(10, @exp) INSERT @t values(0) INSERT @t values(1 * @pow) INSERT @t values(2 * @pow) INSERT @t values(3 * @pow) INSERT @t values(4 * @pow) INSERT @t values(5 * @pow) INSERT @t values(6 * @pow) INSERT @t values(7 * @pow) INSERT @t values(8 * @pow) INSERT @t values(9 * @pow) IF @exp <= 0 BEGIN INSERT @result SELECT * FROM @t -- End recursion. RETURN END SET @exp = @exp - 1 INSERT @result SELECT a.value + b.value FROM @t a cross join fn_p(@exp) b -- Use recursion. RETURN END GO IF EXISTS (SELECT 1 FROM sysobjects WHERE id = object_id('fn_sequence')) DROP function fn_sequence GO CREATE function fn_sequence ( @start int, @end int ) RETURNS @result table(value int) AS BEGIN DECLARE @size int, @exp int -- Calculates the actual size of the sequence. SET @size = @end - @start + 1 IF @size <= 0 return SET @exp = cast(log10(@size) AS int) -- Return the sequence. INSERT @result SELECT @start + d.value FROM fn_p(@exp) d WHERE d.value < @size RETURN END GO -- Test the code. SELECT * FROM fn_sequence(-200, 300) ORDER BY 1