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.

no one ??????

You have to write a stored procedure with a CURSOR and WHILE LOOP to do this.

--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--