CODEWITHSUNDEEP
sqlsql-servert-sql
Alastair Paterson
Alastair Paterson

Reputation: 3

While loop and select

For reasons outside of my control, I need to use SQL server to essentially return numbers in a row. Essentially from 1 to some arbitrary @param, like so:

|-----|
|Value|
|-----|
|  1  |
|  2  |
|  3  |
|-----|

I was trying to use a while loop and selecting the current index from that, however this was returning multiple result sets rather than one result set which won't work. I could accomplish what I'm trying to do by way of a temporary table however would rather not go down that path. There must be some pretty T-SQL way of doing what I'm trying to do.

Thanks in advance for your help :)

Upvotes: 0

Views: 71

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use Tally Table. For more information, read this article by Jeff Moden.

DECLARE @param INT = 1000;

WITH E1(N) AS( -- 10 rows
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 1,0000 rows
E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), -- 100,000,000 rows
Tally(N) AS(
    SELECT TOP(@param)
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM E8 
)
SELECT * FROM Tally

Upvotes: 1

Travis
Travis

Reputation: 699

You can use a CTE to do this by selecting the start value and unioning in the previous value + 1. This is a pretty common tactic for generating sequences.

See this article for a good example: https://smehrozalam.wordpress.com/2009/06/09/t-sql-using-common-table-expressions-cte-to-generate-sequences/

Upvotes: 0

Related Questions