SELECT all N between @min and @max

I need to select all numbers N (integers) between @min and @max Is there any way to achieve that without using a loop of some sort?

Example: Let's say @min = 5, @max = 9

I need the following values returned by my SQL query: 5,6,7,8,9

(I'm using MSSQL 2005)

Thanks!!

EDIT: This is a solution using a custom function, which works fine. But it seems too much effort having to loop through all numbers manually. So the question is still, whether it's achievable without a loop.

CREATE FUNCTION GetAllNBetween
(    
    @Min int,
    @Max int
)
RETURNS @N TABLE(n int)
AS
BEGIN
    W开发者_开发百科HILE @Min <= @Max
    BEGIN
        INSERT INTO @N VALUES(@Min)
        SET @Min = @Min + 1
    END
    RETURN
END

To be used like this:

SELECT * FROM GetAllNBetween(5, 9)

The query (as suggested by @Eric)

select ROW_NUMBER() OVER (ORDER BY so1.id) from sysobjects so1,sysobjects

Returns the numbers between 1 and ~3000, on my mostly empty test database. You can add another level of sysobjects to get an insane number of rows. Then it's a simple matter of filtering this

Well I don't see the point why you don't want to use a loop, but you can use a recursion instead.

DECLARE @min INT
DECLARE @max INT

SET @min = 5;
SET @max = 12;

WITH Nbrs ( n ) AS (
    SELECT @min UNION ALL
    SELECT 1 + n FROM Nbrs WHERE n < @max
)
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )

It will generate a table with all values. Generating a string list out of this shouldn't be too hard ;)

try this:

DECLARE @min int, @max int
SELECT @Min=5,@Max= 9

SELECT TOP (@Max-@Min+1) @Min-1+row_number() over(order by t1.number) as N
FROM master..spt_values t1 
    CROSS JOIN master..spt_values t2

OUTPUT:

N
--------------------
5
6
7
8
9

(5 row(s) affected)

see this previous question: What is the best way to create and populate a numbers table?

You can do it using the between keyword. Here is the example.

select *
  from tableName
 where e_id between (SELECT min(e_id) FROM tableName a)
                and (SELECT max(e_id) FROM tableName a)

And if you know min and max then directly put those instead of nested query.

Try this

declare @min int

set @min= (select 5)

 declare @max int

set @max=(select 9)

select * from table

where id between @min and @max

Can you make use of this function row_number()? It's new in Mssql 2005.

Also I just found out that you can also do this in ms sql 2005:

select *
  from dbo.GetTableOfSequentialIntegers(100)
 where number between 5 and 9

I haven't seen any answers with a CTE (Common Table Expression), so here's one:

WITH RECURSIVE integers(n)
AS (
  SELECT @min
  UNION SELECT n + 1 FROM integers WHERE n < @max
) SELECT n FROM integers