TSQL Maxrecursion on a cte

Question

I get this error:

The maximum recursion 100 has been exhausted before statement completion

when I run this function:

    WITH allDays AS (

        SELECT @DateEarly AS date

        UNION ALL

        SELECT DATEADD(dd, 1, date) as date
        FROM allDays s  
        WHERE DATEADD(dd, 1, date) <= @DateLate


   )
    SELECT *
    from allDays 
    where dbo.isFestivo(date)>0

I tried to append this option:

OPTION (MAXRECURSION 200);

but i get an error 156 before "option" word. Do you know why?

**WHERE EXACTLY** did you *append* this `OPTION (MAXRECURSION 200)` ??? Can you please show us that query you had *with* this option? — marc_s, Nov 15 '12 at 11:25

score 7 · Accepted Answer · answered Nov 15 '12 at 11:25

7

You're probably putting the option in the wrong place. It needs to be after the where

WITH allDays AS (
    SELECT @DateEarly AS date
    UNION ALL
    SELECT DATEADD(dd, 1, date) as date
    FROM allDays s  
    WHERE DATEADD(dd, 1, date) <= @DateLate
)
SELECT *
from allDays 
where dbo.isFestivo(date)>0
option (maxrecursion 200);

But try this instead. It'll be quicker...

select DATEADD(d, number, @dateearly) as [date]
from master..spt_values 
where type='p'
and number<=datediff(d,@dateearly,@datelate)
and dbo.isFestivo(date)>0

answered Nov 15 '12 at 11:25

podiluska

50,950
7
98
104

The second one works (but i had to change dbo.isFestivo(date) with dbo.isFestivo(DATEADD(d, number, @dateearly)))! – Tobia Nov 15 '12 at 14:18
The second way is dangerous since `spt_values` undocumented Microsoft stuff that might be deprecated and removed one day... – hastrb Dec 09 '20 at 14:22

TSQL Maxrecursion on a cte

1 Answers1

Linked