Create dynamic integer resultset for joining

/* ------------------------------------------------
Function: dbo.GetIntRange
Author: Paul Chapman (chappi (dot) uk (at) virgin (dot) net)
Date: 05/03/2004
Purpose: Dynamically generate a resultset of integers for a given range/increment
------------------------------------------------ */
CREATE FUNCTION dbo.GetIntRange(
@lowest int,
@highest int,
@increment int = 1
)
RETURNS @IntRange TABLE (intvalue int)
AS
BEGIN
DECLARE @loopint int
SELECT @loopint = @lowest
WHILE (@loopint <= @highest)
BEGIN
INSERT INTO @IntRange VALUES (@loopint)
SELECT @loopint = @loopint + @increment
END
RETURN
END

Example



SELECT intValue, "Date Format"=LEFT(CONVERT(varchar, GETDATE(), intValue), 30) from dbo.GetIntRange(100, 114, 1)

intValue Date Format
----------- ------------------------------
100 Mar 6 2004 12:13PM
101 03/06/2004
102 2004.03.06
103 06/03/2004
104 06.03.2004
105 06-03-2004
106 06 Mar 2004
107 Mar 06, 2004
108 12:13:28
109 Mar 6 2004 12:13:28:233PM
110 03-06-2004
111 2004/03/06
112 20040306
113 06 Mar 2004 12:13:28:233
114 12:13:28:233

(15 row(s) affected)

I have since found that you should use these functions with caution. They are for conveniently generating virtual data which for is impractical/inconvenient to store in a table, dont use them for large joins as it results in a table scan on the function result which can impact performance.

You can of course use the function to generate the data into a temporary table, then create an index on the temporary table which will perform much better, and still have the benefit of using dynamically generated data