Generating number sequences with Common Table Expressions

,

Unfortunatelly the base script is able to generate sequences of limited length due to SQL Server 2005 limit for 32767 reqursion levels, so for longer sequences I had to fall back on 'cross join'.

/***************************************************************************************
*
* Author Rafal Skotak
* Purpose Generate number sequence (using Common Table Expression)
* Date 2008.02.06
*
***************************************************************************************/


declare @range_begin int
declare @range_end int
declare @range_step int

set @range_begin = 0
set @range_end = 32767
set @range_step = 7;

WITH res(val)
AS
( 
select val from (select @range_begin as val) as resy
union all
select res.val + @range_step from res where res.val < @range_end
)
select val from res option (MAXRECURSION 32767)

/*
 * To generate longer sequences use to following script:
 */

WITH res(val)
AS
( 
select val from (select 0 as val) as resy
union all
select res.val + 1 from res where res.val < 1000
)
select res1.val + res2.val * 1000 as val from res as res1 cross join res as res2 option (MAXRECURSION 32767)

Rate

2.67 (3)

Share

Share

Rate

2.67 (3)