I recently came across the Postgres generate_series function whilst reading a blog post.
So far as I can tell, there’s no equivalent in T-SQL. To make up for this, I coded my own, making use of the recursive nature of common table expressions:
create function dbo.generate_series ( @start bigint , @stop bigint , @step bigint = 1 , @maxResults bigint = 0 --0 = unlimited ) returns @results table(n bigint) as begin --avoid infinite loop (i.e. where we're stepping away from stop instead of towards it) if @step = 0 return if @start > @stop and @step > 0 return if @start < @stop and @step < 0 return --ensure we don't overshoot set @stop = @stop - @step --treat negatives as unlimited set @maxResults = case when @maxResults < 0 then 0 else @maxResults end --generate output ;with myCTE (n,i) as ( --start at the beginning select @start , 1 union all --increment in steps select n + @step , i + 1 from myCTE --ensure we've not overshot (accounting for direction of step) where (@maxResults=0 or i<@maxResults) and ( (@step > 0 and n <= @stop) or (@step < 0 and n >= @stop) ) ) insert @results select n from myCTE option (maxrecursion 0) --sadly we can't use a variable for this; however checks above should mean that we have a finite number of recursions / @maxResults gives users the ability to manually limit this --all good return end
Example Usage:
--check we get expected results select * from generate_series(1, 10, default, default) select * from generate_series(10, 5, -1, default) select * from generate_series(1, 10, 4, default) select * from generate_series(1, 10, default, 2) select * from generate_series(1, 10, 4, -1) --check we don't get results if we have "invalid" input select * from generate_series(1, 10, 0, default) select * from generate_series(10, 1, default, default) select * from generate_series(10, 5, 1, default) select * from generate_series(1, 10, -4, default)
NB: Should you wish to generate a series of dates instead of a series of numbers, check my comments here: http://stackoverflow.com/questions/1478951/generate-a-resultset-of-incrementing-dates-in-tsql/25881077#25881077
Leave a comment