How can we avoid the error
“Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 3 has been exhausted before statement completion. ”
if cte goes above 100 levels, then what need to do?
Ans: Set the MAXRECURSION hint of CTE query to any nonnegative value between 0 and 32767 as below:
SELECT EmployeeID, ManagerID, Title
FROM cteEmployee
OPTION (MAXRECURSION 200);
When 0 is specified, no limit is applied. If this option is not specified, the default limit is 100.
“Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 3 has been exhausted before statement completion. ”
if cte goes above 100 levels, then what need to do?
Ans: Set the MAXRECURSION hint of CTE query to any nonnegative value between 0 and 32767 as below:
SELECT EmployeeID, ManagerID, Title
FROM cteEmployee
OPTION (MAXRECURSION 200);
When 0 is specified, no limit is applied. If this option is not specified, the default limit is 100.
No comments:
Post a Comment