generate_series for SQL Server 2008

A few years ago I wrote an article about implementing the PostgreSQL function, generate_series, in Oracle. This function is really useful for when trying to write SQL/Functions that can be implemented across one or more databases with reasonable ease.

Here is the function implemented in SQL Server 2008:

 -- Connect to database holding generate_series
 --
 USE [GISDB] -- Change to your database
 GO
 -- Drop function if exists
 --
 IF EXISTS (SELECT *
              FROM dbo.sysobjects
             WHERE id = object_id (N'[dbo].[generate_series]')
               AND OBJECTPROPERTY(id, N'IsTableFunction') = 1)
 DROP FUNCTION [dbo].[generate_series]
 GO
 --
 -- Now let's create it
 --
 CREATE FUNCTION [dbo].[generate_series] ( @p_start INT, @p_end INT, @p_step INT=1 )
 RETURNS @Integers TABLE ( [IntValue] INT )
 AS
 BEGIN
     DECLARE
       @v_i                 INT,
       @v_step              INT,
       @v_terminating_value INT;
     BEGIN
       SET @v_i = CASE WHEN @p_start IS NULL THEN 1 ELSE @p_start END;
       SET @v_step = CASE WHEN @p_step IS NULL OR @p_step = 0 THEN 1 ELSE @p_step END;
       SET @v_terminating_value = @p_start + CONVERT(INT,ABS(@p_start-@p_end) / ABS(@v_step) ) * @v_step;
       -- Check for impossible combinations
       IF NOT ( ( @p_start > @p_end AND SIGN(@p_step) = 1 )
                OR
                ( @p_start < @p_end AND SIGN(@p_step) = -1 ))
       BEGIN
         -- Generate values
         WHILE ( 1 = 1 )
         BEGIN
            INSERT INTO @Integers ( [IntValue] ) VALUES ( @v_i )
            IF ( @v_i = @v_terminating_value )
               BREAK
            SET @v_i = @v_i + @v_step;
         END;
       END;
     END;
     RETURN
 END
 GO

Now, let’s test this function.

 -- Simple increment by 1
 SELECT g.IntValue as generate_series
   FROM generate_series(2,4,1) g;
 -- Results
 generate_series
 2
 3
 4
 -- Increment using Default value (1)
 SELECT g.IntValue as generate_series
   FROM generate_series(1,5,DEFAULT) g;
 -- Results
 generate_series
 1
 2
 3
 4
 5
 -- Increase in steps of 10
 SELECT g.IntValue as generate_series
   FROM [generate_series] ( 100, 200, 10 ) g;
 -- Results
 generate_series
 100
 110
 120
 130
 140
 150
 160
 170
 180
 190
 200
 -- Negative decrement
 SELECT g.IntValue as generate_series
   FROM generate_series(5,1,-2) g;
 -- Results
 generate_series
 5
 3
 1
 -- Invalid test
 SELECT g.IntValue as generate_series
   FROM generate_series(4,3,1) g;
 -- Results
 generate_series
 -- Increment negative numbers
 SELECT g.IntValue as generate_series
   FROM generate_series(-4,-1,1) g;
 -- Results
 generate_series
 -4
 -3
 -2
 -1
 -- generate some dates
 SELECT convert(varchar(20),GETDATE() + sa.IntValue,112) as dates
   FROM generate_series(0,14,7) sa;
 -- Results
 dates
 20120410
 20120417
 20120424
 -- Create some points
 SELECT f.id,
        geometry::Point(f.x,f.y,0 /* SRID */ ).STAsText() as geom
   FROM ( SELECT sa.IntValue as id,
                 ROUND(10000 * RAND(CHECKSUM(NEWID())) + 353880,2) as x,
                 ROUND(5000 * RAND(CHECKSUM(NEWID())) + 5404973,2) as y
            FROM dbo.generate_series(1,25,1) sa
        ) f
 order by 1 asc;
 -- Results
 id geom
 1 POINT (363011.9 5406135.94)
 2 POINT (361741 5408272.44)
 3 POINT (363844.43 5408793.59)
 4 POINT (356034.42 5405077.86)
 5 POINT (360968.83 5408948.45)
 6 POINT (363247.97 5408120.85)
 7 POINT (363265.25 5409078.93)
 8 POINT (355839.57 5405431.12)
 9 POINT (358031.78 5408748.88)
 10 POINT (362166.17 5408519.15)
 11 POINT (357931.48 5405419.99)
 12 POINT (362586.12 5404990.02)
 13 POINT (359782.46 5405622.41)
 14 POINT (354394.21 5406409.27)
 15 POINT (355763.87 5406505.26)
 16 POINT (357572.38 5407954.41)
 17 POINT (356033.02 5408066.5)
 18 POINT (360874.83 5407605.39)
 19 POINT (360484.8 5406577.3)
 20 POINT (359978.63 5404987.73)
 21 POINT (363641.33 5407022.35)
 22 POINT (357245.02 5407018.61)
 23 POINT (355047.11 5407032.75)
 24 POINT (357383.37 5405070.77)
 25 POINT (356912.68 5408711.83)
 -- Extract Points from a geometry
 SELECT sa.IntValue as pointN,
        a.geom.STPointN(sa.IntValue).STX as x,
        a.geom.STPointN(sa.IntValue).STY as y
   FROM (select geometry::STGeomFromText('LINESTRING(0 0,1 0,1 1,2 1,2 2)',0) as geom) a
        cross apply
        dbo.generate_series(1,a.geom.STNumPoints(),1) sa;
 -- Results
 pointN x y
 1 0 0
 2 1 0
 3 1 1
 4 2 1
 5 2 2

I hope this function is useful to someone.