SQL query to return 1 through int variable

Question

in SQL Server, if I have an int variable, how can I write a query that returns the values 1 through the int variable's value?

So given:

declare @cnt int
set @cnt = 5

What query would return 1 - 5? Is this possible?

Answer 1

You could do a WHILE loop in SQL like this:

DECLARE @Cnt int, 
        @Val int;
SET @Cnt = 5;
SET @Val = 0;

DECLARE @MyValues TABLE
(
  val int
);

WHILE @Val < @Cnt
 BEGIN
  SET @Val = @Val + 1;
  INSERT @MyValues(val)
  SELECT @Val;
 END;
SELECT Val FROM @MyValues;

Answer 2

declare @cnt int
    set @cnt = 0
    while @cnt < 5
    begin
      set @cnt = @cnt + 1
      print 'The counter is ' + cast(@counter as char)
    end

Answer 3

You can easily implement an "output" value just using at the end of your query a SELECT statment...

SELECT ... FROM ... WHERE ... Bla Bla, yadah, yadah;
SELECT (1 - @cnt);

and you will have the output that you want.

Answer 4

You can do it in one query using a recursive CTE, but you need to be aware of the MAXRECURSION query hint if your @cnt starts at a very high value.

DECLARE @cnt int;
SET @cnt = 5;

WITH Numbers AS (
    SELECT @cnt [Value]

    UNION ALL

    SELECT [Value] - 1
    FROM Numbers
    WHERE [Value] > 1
)
SELECT * FROM Numbers
ORDER BY [Value]

Having said that, though, doing it with a simple WHILE loop would be simpler and easier to read.

Answer 5

Didn't know whether you wanted just one resultset (as per José Basilio's answer) or were happy with more than one

DECLARE @ctr INT
DECLARE @start INT

SET @ctr = 5
SET @start = 1

WHILE @start <= @ctr
BEGIN
   SELECT @start
   SET @start = @start + 1
END

Answer 6

Here's a function to do that:

CREATE FUNCTION [dbo].[Sequence] (@start int, @end int, @step int)  
RETURNS @result table (num int) AS  
BEGIN 
    declare @num int
    set @num = @start

    while @num <= @end
        begin
        insert into @result (num) values (@num)
        set @num = @num + @step
        end
    return
END

With the function, you can select sequences like:

select * from dbo.Sequence(1,5,1)

This returns 1,2,3,4,5.

select * from dbo.Sequence(2,6,2)

Will return 2,4,6. And so on :)

Answer 7

An alternative answer to the others already given, maybe not so practical but it may be faster in certain situations.

If you have a known upper bound for the values of @cnt you will encounter (say 1000) you could create a simple one-column table with the first 1000 integers in it, like

val
---
1
2
3
4
...

and then whenever you want a set of integers you could get it using

select val
from integerTable
where val <= @cnt

Answer 8

If the maximum value of @cnt in your context is relatively low, you could populate a temporary or persistent table of integers and select from or join to it.

CREATE TABLE LotsOfNumbers(Number int not null)
INSERT INTO LotsOfNumbers VALUES(1)
INSERT INTO LotsOfNumbers VALUES(2)
INSERT INTO LotsOfNumbers VALUES(3)
...
INSERT INTO LotsOfNumbers VALUES(99)
INSERT INTO LotsOfNumbers VALUES(100)


DECLARE @cnt INT
SET @cnt = 30

SELECT *
FROM LotsOfNumbers
WHERE Number <= @cnt
ORDER BY Number

Of course, the time/space performance of this is going to depend on your situation, so I'd watch SQL Profiler to get an idea of the hit on the DB for each of these methods.

Answer 9

Generating a table of a single columns with numbers is easy.

Create Table Numbers(Number INT);

INSERT INTO Numbers(Number) values(0);
INSERT INTO Numbers(Number) values(1);
INSERT INTO Numbers(Number) values(2);
.
.
INSERT INTO Numbers(Number) values(9);

-- To generate a list of 100000

SELECT * INTO TEST
FROM
(SELECT N1.Number * 1000 +  N2.Number * 100 + N3.Number * 10 + N4.Number * 1 as Num
FROM   Numbers N1
CROSS JOIN Numbers N2
CROSS JOIN Numbers N3
CROSS JOIN Numbers N4) AS N
ORDER BY Num;

--Then, You can use the above table for getting the numbers
SELECT * from TEST WHERE Num <= 5;