Using max recursion within a CTE to produce a CTE rather than in the final SELECT statement only

Question

I want to produce a column of dates between a predetermined start and end date and store the result in a CTE to use within my query. The recursive method below does work but using maxrecursion within a CTE doesn't seem to be possible. Is there another way around this?

(1) This works fine:

DECLARE @startnum INT=1
DECLARE @endnum INT=10000
;
WITH gen AS (       
    SELECT @startnum AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=@endnum
)

SELECT * FROM gen
option (maxrecursion 10000)

(2) This won't work:

DECLARE @StartDate DATE; 
DECLARE @EndDate DATE;
SET @StartDate = '20150406'; 
SET @EndDate = '20190406';

DECLARE @startnum INT=1
DECLARE @endnum INT=10000
;
WITH gen AS (       
    SELECT @startnum AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=@endnum
)
,

num_list as (
SELECT * FROM gen
option (maxrecursion 10000)
)
,

tab_dates as (
SELECT  DATEADD(DAY, nbr - 1, @StartDate) AS Dates
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.num) AS Nbr
          FROM      num_list c
        ) nbrs
WHERE   nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)
)
SELECT * FROM tab_dates

Have you considered using a tally instead? Then you don't have an RBAR solution, and don't have to worry about the maxiy recursion. — Thom A, Apr 13 '19 at 22:11
Creating an *on-the-fly-tally* (Larnu's answer) is a very good approach. But - if this is possible for you - I'd suggest to create a *physical number/date-table* instead. With appropriate indexes this will be lightning fast and allows very easy solutions for repeatedly coming issues. You find an example [here](https://stackoverflow.com/a/32474751/5089204). You simple join this table and limit the rows count with a simple `WHERE` or a `TOP` clause. — Shnugo, Apr 14 '19 at 09:37

score 3 · Answer 1 · answered Apr 13 '19 at 22:15

Rather than using a RBAR rCTE, use a Tally. you can use a CTE to do this too. You don't suffer the max recursion issue that way, and it's much faster:

WITH N AS (
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1 --10
         CROSS JOIN N N2 --100
         CROSS JOIN N N3 --1000
         CROSS JOIN N N4 --10000
    )
SELECT *
FROM Tally;

With the above example you can keep making further CROSS JOIN's to N in the CTE tally and you will increase the number of rows by a multiple of 10.

score 2 · Accepted Answer · answered Apr 13 '19 at 22:09

You add the maxrecursion option at the end of the final select. The option applies to all the CTEs.

Think of the whole thing as a single statement, which it is.

I know this seems counterintuitive. I was confused the first time I came across this problem.

Using max recursion within a CTE to produce a CTE rather than in the final SELECT statement only

2 Answers2