Transact SQL :- Return set of numbers

Background

Upon seeing the ease with which one is able to generate series of Numbers and Dates with PostgreSQL generate_series function, googled for same in SQL Server \ Transact SQL.

Working Version

Sebastian Webber

Sebastian Webber has a working version.

Where else but StackOverlow or GitHub.

Here is the Gist URL :-

sebastianwebber/generate_series.sql
Link

Code

Here is Sebastian’s code :-


/*
    sebastianwebber/generate_series.sql
*/
IF EXISTS (SELECT *
             FROM dbo.sysobjects
            WHERE id = object_id (N'[dbo].[generate_series]')
              AND OBJECTPROPERTY(id, N'IsTableFunction') = 1)
DROP FUNCTION [dbo].[generate_series]
GO

CREATE FUNCTION [dbo].[generate_series] ( @p_start INT, @p_end INT)
RETURNS @Integers TABLE ( [IntValue] INT )
AS
BEGIN

  WITH interval(V) AS (
		SELECT @p_start
		UNION ALL
		SELECT V + 1 FROM interval
		WHERE V < @p_end
	)
	INSERT INTO @Integers
	SELECT * FROM interval;

    RETURN
END
GO

Code

Outline

  1. We have a couple of functions
    • dbo.itvf_generateSeries_Number
      • Generates a sequence of numbers
    • dbo.itvf_generateSeries_NumberAndSequence
      • Generates a sequence of numbers
      • Attaches the sequence number

dbo.itvf_generateSeries_Number


IF NOT EXISTS
(
    SELECT 1

    FROM   sys.objects tblSO

    WHERE  tblSO.object_id = object_id (N'[dbo].[itvf_generateSeries_Number]')

    AND    tblSO.[type] = 'IF'

)
begin

exec
    ('CREATE FUNCTION [dbo].[itvf_generateSeries_Number]
        (
              @lStart BIGINT
            , @lEnd   BIGINT
        )
        RETURNS TABLE
        AS
        RETURN
        (
            select 1 as [vail]
        )
    ')

end

GO

ALTER FUNCTION [dbo].[itvf_generateSeries_Number]
(
      @lStart BIGINT
    , @lEnd   BIGINT
)
RETURNS TABLE
AS
RETURN

(

  /*
    sebastianwebber/generate_series.sql
  */
  with cteInterval
  (
    [number]
  )
  AS
  (
	    SELECT @lStart

        UNION ALL

        SELECT [number] + 1 

        FROM   cteInterval

        WHERE  [number] < @lEnd
	)

	SELECT [number]

    FROM   cteInterval cteI

)
go

grant select on [dbo].[itvf_generateSeries_Number] to [public]
go

dbo.itvf_generateSeries_NumberAndSequence


set nocount on;
go

set quoted_identifier on
go

IF NOT EXISTS
(
    SELECT 1

    FROM   sys.objects tblSO

    WHERE  tblSO.object_id = object_id (N'[dbo].[itvf_generateSeries_NumberAndSequence]')

    AND    tblSO.[type] = 'IF'

)
begin

exec
    ('CREATE FUNCTION [dbo].[itvf_generateSeries_NumberAndSequence]
        (
              @lStart BIGINT
            , @lEnd   BIGINT
        )
        RETURNS TABLE
        AS
        RETURN
        (
            select 1 as [vail]
        )
    ')

end

GO

ALTER FUNCTION [dbo].[itvf_generateSeries_NumberAndSequence]
(
      @lStart BIGINT
    , @lEnd   BIGINT
)
RETURNS TABLE
AS
RETURN

(

  /*
        sebastianwebber/generate_series.sql
  */

  with cteInterval
  (
      [number]
    , [sequence]
  )
  AS
  (
	    SELECT
                  [nunber] = @lStart
                , [sequence] = 1

        UNION ALL

        SELECT
                  [nunber] = [number] + 1

                , [sequence] = cteI.[sequence] + 1

        FROM   cteInterval cteI

        WHERE  [number] < @lEnd
	)

	SELECT
              [number]

            , [sequence]

    FROM   cteInterval cteI

)
go

grant select on [dbo].[itvf_generateSeries_NumberAndSequence] to [public]
go

Source Code Control

GitHub

  1. DanielAdeniji/TransactSQLGenerateSeries
    Link

    • Sebastian Webber
      Link
    • Daniel Adeniji
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s