Listing 1: CreateIntegersTable

USE [master]
GO

IF OBJECTPROPERTY(OBJECT_ID('dbo.CreateIntegersTable'),'IsTableFunction') = 1
  BEGIN
    DROP FUNCTION dbo.CreateIntegersTable
  END
GO

CREATE FUNCTION [dbo].[CreateIntegersTable]
(
  @start_int  BIGINT = 1,
  @step_int  BIGINT = 1,
  @end_int  BIGINT
)

RETURNS @FinishedIntegersTable TABLE
(
  ints  BIGINT
)

AS
BEGIN

/*  Table variable @IntegersTable holds finished Integer list. */

  DECLARE @IntegersTable TABLE 
      (
        ints BIGINT
      )

/*  If the user picked an @start_int value less than 1, shift the @end_int value. */

  IF (@start_int < 1)
    SET @end_int = (@end_int + ABS(@start_int) + 1);

/*  Common table expression to build list of Integers less than 32,767. */

  WITH IntegersTableFill (ints) AS
  (
    SELECT

      CAST(1 AS BIGINT) AS 'ints'

    UNION ALL

    SELECT  (T.ints + @step_int) AS 'ints'
    FROM  IntegersTableFill T
    WHERE  ints <= (
              CASE
                WHEN  (@end_int <= 32767) THEN @end_int
                ELSE  32767
              END
            )
  )

  INSERT INTO @IntegersTable
  SELECT ints
  FROM IntegersTableFill
  OPTION ( MAXRECURSION 32767 )

/*  This block handles @end_int > 32,767. */

  IF (@end_int > 32767)
  BEGIN

    DECLARE @last_int_inserted  BIGINT
    DECLARE @int_row_groups    INT
    DECLARE @current_row_group  SMALLINT

/*    Figure out how many row groups the function needs for @end_int rows. */

    SELECT @int_row_groups = CEILING((LOG((@end_int * 1.0)/65534))/LOG(2)) + 1

/*    The WHILE loop uses variable @int_row_groups. */

    SET @current_row_group = 1

    WHILE (@current_row_group <= @int_row_groups)
    BEGIN

      SELECT @last_int_inserted = MAX(ints) FROM @IntegersTable

/*
      In each group, make row inserts into @IntegersTable,
      starting at @last_int_inserted and ending at
      @end_int in the last group.
 */

      INSERT INTO @IntegersTable
      SELECT  ints + @last_int_inserted + (@step_int - 1)
      FROM  @IntegersTable
      WHERE  (ints + @last_int_inserted) <= @end_int

      SET @current_row_group = @current_row_group + 1

    END
  END

/*  Reverse the Integer shift in @IntegersTable, then in @end_int. */

  IF (@start_int <> 1)
 

    UPDATE  @IntegersTable
    SET    ints = (ints + (SIGN(@start_int) * ABS(@start_int)) - 1)

  IF (@start_int < 1)
    SET @end_int = (@end_int - ABS(@start_int) - 1)

/*  Trim @IntegersTable, then set up the function return value. */

  DELETE FROM @IntegersTable
  WHERE  ints < @start_int OR
      ints > @end_int

  INSERT @FinishedIntegersTable SELECT ints FROM @IntegersTable

  RETURN

END