Generate a sequence of numbers
(Recursive) Common table expressions can be used to generate a sequence of numbers with SQL.
The following example generates the numbers from 11 up to a maximum of 10000 in steps of 3.
Because SQL Server puts a default limit of 100 recursions, unlimited recursions need to be enabled with option (maxrecurion 0). Otherwise, we'd get the error message The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
with num as (
select 11 ber -- Start number
union all
select 3 + ber -- Increment
from num
where 3 + ber < 10000 -- Maximal returned number
)
select
num.ber
from
num
--
-- Prevent error message
-- «The statement terminated.
-- The maximum recursion 100 has been exhausted before statement completion.»
--
option (maxrecursion 0)
;