SpatialDB Advisor: generate_series for SQL Server 2008

generate_series for SQL Server 2008

· Nov 20, 04:49 pm by Simon Greener

I recently wrote an article about implementing the PostgreSQL function, generate_series, in Oracle.

Here is the function implemented in SQL Server 2008:

DROP FUNCTION [generate_series]
GO

CREATE FUNCTION [generate_series] ( @p_start INT, @p_end INT, @p_step INT=1 )
RETURNS @Integers TABLE ( [IntValue] INT )
AS
BEGIN
    DECLARE @v_i                 INT;
    SET @v_i = CASE WHEN @p_start IS NULL THEN 1 ELSE @p_start END;
    DECLARE @v_step              INT;
    SET @v_step  = CASE WHEN @p_step IS NULL OR @p_step = 0 THEN 1 ELSE @p_step END;
    DECLARE @v_terminating_value INT;
    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
       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;
    RETURN
END
GO

SELECT g.IntValue
  FROM [generate_series] ( 100, 500, 10 ) g;

SELECT g.IntValue as generate_series 
  FROM generate_series(1,5,DEFAULT) g;

SELECT g.IntValue as generate_series 
  FROM generate_series(2,4,1) g;

SELECT g.IntValue as generate_series 
  FROM generate_series(5,1,-2) g;

SELECT g.IntValue as generate_series 
 FROM generate_series(4,3,1) g;

SELECT g.IntValue as generate_series 
  FROM generate_series(-4,-1,1) g;
  
SELECT convert(varchar(20),GETDATE() + sa.IntValue,112) as dates 
  FROM generate_series(0,14,7) sa;