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).
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. ...