I have a query that exhausts the default MAXRECURSION
limit of 100. Giving me the following error message:
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
I have found out that I need to raise the limit for this CTE using OPTION (MAXRECURSION xxx)
but I don't know where to put this.
So far I've tried to put it next to where I define the CTE but it isn't working. I've also tried a few different places and it's not working either. The error I get each time is:
Incorrect syntax near the keyword 'OPTION'.
So where should I put the OPTION (MAXRECURSION XXX)
command in my SQL?
with
tab (id,start,en) AS (
SELECT 1, 100, 200
UNION ALL SELECT 2, 200, 500
),
cte (id,start,en) AS (
SELECT id, start, en FROM tab
UNION ALL
SELECT id, start+1, en FROM cte WHERE start+1 <= en
)
SELECT id, start
FROM cte
ORDER BY id