Microsoft® SQL Server 2012 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 46.11).

Note

To run the examples against the PART table, you’ll first need to run the Create_Parts_Table.sql script located onlinein the Sample Databases subfolder in the Code Listings and Sample Files folder. ...