Limiting Recursion
When you have recursion in any environment, you risk causing infinite recursive calls. Such a situation might happen as a result of a logical bug in your code—for example, if your Recursive Member returns the same rows as the Anchor Member. Infinite recursive calls can also result from invalid data entered into your database. For example, suppose your table contains data specifying that Employee 10 manages Employee 11 and Employee 11 manages Employee 10. Such a scenario is called a cyclic relationship. SQL Server 2005 doesn't provide a tool that detects cyclic relationships, but it does let you limit the number of recursive iterations to prevent infinite recursive calls. You can add the MAXRECURSION option in an OPTION clause you specify at the end of the outer query and provide a number that determines the maximum number of invocations for the Recursive Member. SQL Server 2005 defaults to a maximum of 100 recursion calls if you don't specify a value. If you specify 0, you remove any limitation on the number of recursive calls.
As an example of using the MAXRECURSION option, run the code in Listing 7 to return Employee 109 and direct and indirect subordinates, with a limit of three recursive calls. Note that if the number of recursive invocations reaches the limit you specify in the MAXRECURSION option before the recursive member returns an empty set (normal recursion termination), your code fails and SQL Server returns the following error message:
.Net SqlClient Data Provider: Msg 530, Level 16, State 1, Line 1
Statement terminated. Maximum recursion 3 has been exhausted before statement completion.
In such a case, you're not guaranteed to get any result set. Therefore, don't use the MAXRECURSION option if you want to logically limit the number of returned levels (as opposed to using it as a safety measure against infinite recursive calls). For example, if you want to return only three levels of subordinates below Employee 109, you should devise a custom solution instead of using MAXRECURSION.
An efficient way to logically limit the number of levels to return is to generate a pseudo level column (call it lvl). You return a 0 value in the pseudo lvl column in the Anchor Member, return lvl + 1 in the same column in the Recursive Member, and specify lvl < level_limit in the Recursive Member's filter. To see how to use the pseudo lvl column, run the code in Listing 8, which returns Employee 109 and subordinates three levels down.
The real power of CTEs lies in their recursive manipulation of data. CTEs let you write short, efficient, ANSI-compliant code to manipulate hierarchies. In my next column, I'll show you how to manipulate multiparent hierarchies, then I'll explore advanced concepts related to recursive CTEs.
End of Article
Prev. page
1
2
[3]
next page -->
