# How to generate a range of numbers between two numbers?

157

I have two numbers as input from the user, like for example `1000` and `1050`.

How do I generate the numbers between these two numbers, using a sql query, in seperate rows? I want this:

`````` 1000
1001
1002
1003
.
.
1050
``````

This question is tagged with `sql` `sql-server` `tsql` `sql-server-2008`

178

Select non-persisted values with the `VALUES` keyword. Then use `JOIN`s to generate lots and lots of combinations (can be extended to create hundreds of thousands of rows and beyond).

``````SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
WHERE ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n BETWEEN @userinput1 AND @userinput2
ORDER BY 1
``````

Demo

A shorter alternative, that is not as easy to understand:

``````WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
FROM x ones,     x tens,      x hundreds,       x thousands
ORDER BY 1
``````

Demo

115

an alternative solution is recursive CTE:

``````DECLARE @startnum INT=1000
DECLARE @endnum INT=1050
;
WITH gen AS (
SELECT @startnum AS num
UNION ALL
SELECT num+1 FROM gen WHERE num+1<[email protected]
)
SELECT * FROM gen
option (maxrecursion 10000)
``````