Last post Aug 18, 2016 01:32 PM by limno
Aug 18, 2016 07:02 AM|ssjGanesh|LINK
I have written Anchor and Recursive query within CTE, I am getting this error "The maximum recursion 100 has been exhausted before statement completion."
Select Id,Name,0 as [Level] from Table1
Select Id,Name,1 as [Level] from Table1 A Inner join SampleCTE C on A.Id <> C.Id
Select * from SampleCTE where Level <>0
What I tried:
I have added one condition in SampleCTE and restricted the recurssion within 100 like below .
Select Id,Name,0 as [Level],1 as RowNo from Table1
where Level <100
Now I dont get that error but My question is...
"Will I get all reacords or I will get only some of the records from the recurssion?"
Will there any records wont come because of the recurssion stopped.?
Aug 18, 2016 08:20 AM|eralper|LINK
You can simply add MaxRecursion option after FROM clause for CTE expression
Please check article for The statement terminated. The maximum recursion 100 has been exhausted before statement completion
You can use it as follows
SELECT i FROM CTE_NumbersTable OPTION (MAXRECURSION 0)
Aug 18, 2016 09:21 AM|ssjGanesh|LINK
Thanks for ur reply.
If I adding a where condition in the CTE like,
SELECT * FROM SampleCTE OPTION (MAXRECURSION 0) where Level <> 0Then I am getting sysntax error.
Aug 18, 2016 01:32 PM|limno|LINK
;With SampleCTE as (Select...
where Level <>
OPTION (MAXRECURSION 0)