jacroberts (11/24/2008)
I'd consider using a table valued function instead that only returns the rows I'm interested in:e.g.:
CREATE FUNCTION [dbo].[Nums]
(
@StartNum int,
@EndNum int
)
RETURNS @table TABLE (Num int)
AS BEGIN
DECLARE @tmpNum int
SET @tmpNum = @StartNum
WHILE @tmpNum < @EndNum
BEGIN
INSERT INTO @table VALUES (@tmpNum)
SET @tmpNum = @tmpNum + 1
END
RETURN
END
GO
Example Usage:
SELECT * FROM Nums(1000, 2000)
Ummm... have you actually tried using one with a WHILE loop like that? A cross-joined CTE will be much quicker. The whole purpose of a Numbers or Tally table is to get rid of loops.
--Jeff Moden
Change is inevitable... Change for the better is not.