michael vessey (5/2/2012)
wouldn't this be easier to do with a recursive CTE?declare @start bigint=99;
declare @end bigint=147;
with x (num) as
(
select 1 as num
UNION ALL
SELECT num+1 from x
where num <@end
)
select * from x where num between @start and @end
option (maxrecursion 10000);
Or . . .
DECLARE @start BIGINT = 97;
DECLARE @end BIGINT = 250000;
WITH CTE(n) AS(SELECT 1 UNION ALL SELECT 1),
CTE2(n) AS (SELECT 1 FROM CTE x, CTE y),
CTE3(n) AS (SELECT 1 FROM CTE2 x, CTE2 y),
CTE4(n) AS (SELECT 1 FROM CTE3 x, CTE3 y),
CTE5(n) AS (SELECT 1 FROM CTE4 x, CTE4 y),
CTE6(n) AS (SELECT 0 UNION ALL
SELECT TOP (@end-@start)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM CTE5 x, CTE5 y)
SELECT @start+n
FROM CTE6
WHERE @start+n <= @end;
Or. . .
DECLARE @start BIGINT = 97;
DECLARE @end BIGINT = 1000000;
SELECT @start+n
FROM (SELECT 0 UNION ALL
SELECT TOP (@end-@start)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master.sys.columns a, master.sys.columns b, master.sys.columns c) a(n)
WHERE @start+n <= @end;