|
generate_series for SQL Server 2008
Thursday November 20 2008 at 16:49
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;
        
|
SpatialDB Advisor
Home
Comment [4]
Simon,
Finally got a chance to look at this but haven’t taken it thru paces yet. It seems your editor is stripping the @
sign. The above function didn’t compile for me until I changed all the variables to have @ in front of them.
— Regina · 21 November 2008, 05:36 · #
Regina,
Thanks for pointing out the lack of @ symbols. Yes, Textile was removing them: I have fixed this so all should now be well.
Simon
— Simon · 21 November 2008, 09:22 · #
Hi Simon,
The above function looks promising, but I get syntax errors in SSMS – specifically, line 10, syntax error near ‘-=’
Is this line correct? ABS
Cheers,
Kevin
— Kevin · 18 July 2009, 05:51 · #
Kevin,
Thanks for pointing out the problem which crept in because of Textpattern formatting. Now fixed. Thanks
Simon
— Simon · 18 July 2009, 11:09 · #