Need to put OPTION (MAXRECURSION XXX)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need to put OPTION (MAXRECURSION XXX)

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2010-07-27 : 23:55:02

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.

Any ideas?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-28 : 00:33:09

refer to BOL example http://msdn.microsoft.com/en-us/library/ms175972.aspx


FROM   cte
OPTION (MAXRECURSION 1000)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources