Microsoft® SQL Server 2008 R2 Unleashed

Recursive Stored Procedures

A stored procedure can call itself up to the maximum nesting level of 32. This is referred to as recursion. When might you want a stored procedure to be recursive? One common example is when you need to expand a tree relationship. Although a common table expression (CTE) can be used to recursively expand a tree relationship, internally it builds the entire tree before applying any filters to display the tree, starting at a specific level. It is also somewhat limited in how the tree is displayed (see Listing 44.10).

Listing 44.10 Using a Common Table Expression to Expand a Tree Relationship

A recursive procedure can ...