Results 1 to 4 of 4

Thread: Generate series from a range using procedure

  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Generate series from a range using procedure

    I have table which contains order number and corresponding Serial number range for those order.

    I need to create another table where I can store order number with a list of all serial numbers for that order.

    Table I have:

    Order No SerialNoStart SerialNoEnd
    1 55 66
    2 77 90
    3 233 237

    Table I need to generate from above table:

    Order No SerialNumber
    1 55
    2 56
    3 57
    | |
    | |
    3 66
    4 77
    4 78
    | |
    | |
    4 90
    5 233
    5 234
    | |
    | |
    5 237

    It would be great if anyone can show me how to get this done. I dont care if it's done using function, procedure, etc. whatever is easy.

    Thanks.

  2. #2
    Join Date
    Apr 2011
    Posts
    2
    no one ??????

  3. #3
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You have to write a stored procedure with a CURSOR and WHILE LOOP to do this.

  4. #4
    Join Date
    Sep 2005
    Posts
    168
    --option 1: use a numbers auxiliary table
    CREATE TABLE dbo.Nums(n INT PRIMARY KEY CLUSTERED)
    GO

    INSERT INTO dbo.Nums(n)
    SELECT TOP 100000 ROW_NUMBER() OVER(ORDER BY (SELECT 0))
    FROM sys.all_columns AS a
    CROSS JOIN sys.all_columns AS b
    GO

    DECLARE @orders TABLE(OrderNo INT, SerialNoStart INT, SerialNoEnd INT)
    INSERT INTO @orders(OrderNo, SerialNoStart, SerialNoEnd) VALUES (1, 55, 66)
    INSERT INTO @orders(OrderNo, SerialNoStart, SerialNoEnd) VALUES (2, 77, 90)
    INSERT INTO @orders(OrderNo, SerialNoStart, SerialNoEnd) VALUES (1, 233, 237)


    SELECT o.OrderNo, nm.n
    FROM @orders AS o
    JOIN dbo.Nums AS nm ON o.SerialNoStart <= nm.n AND o.SerialNoEnd >= nm.n

    --option 2: use a helper function
    CREATE FUNCTION dbo.fn_range_Nums(@start INT, @end INT)
    RETURNS TABLE
    AS
    RETURN
    (
    WITH
    T0 AS (SELECT 1 AS c UNION ALL SELECT 1),
    T1 AS (SELECT 1 AS c FROM T0 AS A CROSS JOIN T0 AS B),
    T2 AS (SELECT 1 AS c FROM T1 AS A CROSS JOIN T1 AS B),
    T3 AS (SELECT 1 AS c FROM T2 AS A CROSS JOIN T2 AS B),
    T4 AS (SELECT 1 AS c FROM T3 AS A CROSS JOIN T3 AS B),
    T5 AS (SELECT 1 AS c FROM T4 AS A CROSS JOIN T4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS n FROM T5),
    nrange AS (SELECT TOP (CAST(@end AS INT)) n FROM Nums ORDER BY n)
    SELECT n
    FROM nrange
    WHERE n >= @start
    )

    GO


    DECLARE @orders TABLE(OrderNo INT, SerialNoStart INT, SerialNoEnd INT)
    INSERT INTO @orders(OrderNo, SerialNoStart, SerialNoEnd) VALUES (1, 55, 66)
    INSERT INTO @orders(OrderNo, SerialNoStart, SerialNoEnd) VALUES (2, 77, 90)
    INSERT INTO @orders(OrderNo, SerialNoStart, SerialNoEnd) VALUES (1, 233, 237)


    SELECT o.OrderNo, fn.n
    FROM @orders AS o
    CROSS APPLY dbo.fn_range_Nums(SerialNoStart, SerialNoEnd) AS fn

    --HTH--

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •