Need to put OPTION (MAXRECURSION XXX)

Question

Hi there

I have the following query and I need to put OPTION (MAXRECURSION 1000) on the CTE section cause that place is where is the problem is (UNION ALL). So I put next to that line and compiled and not working and try different place is not working either (Incorrect syntax near the keyword 'OPTION'.). My question where I should put this into OPTION (MAXRECURSION XXX) ?

with tab AS 
(
    select 1 as id, 100 as start, 200 as en
    union all
    select 2, 200, 500),
    cte 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

Note: If you are taking out union all select 2, 200, 500 from that, it works nicely because it's only up to 100.

Answer 1

with tab AS 
(
    select 1 as id, 100 as start, 200 as en
    union all
    select 2, 200, 500),
    cte 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
OPTION (MAXRECURSION 1000)