In the article "Manipulating Hierarchies with UDFs" (January 2001), I presented a nonrecursive UDF called ufn_GetSubtree that returns a whole subtree under a given manager. In the sidebar "Test Your Recursion Powers!" I presented the following challenge: "To test your understanding of recursion and user-defined functions (UDFs), solve the following puzzle: Can you implement the function ufn_GetSubtree by using a recursive algorithm? Note that you aren’t required to return the lvl and path columns, rather just employee details of the whole subtree under a given manager." The recursive ufn_GetSubtree function returns a table variable called @tree with the same schema as the Employees table. The function performs the following steps:
- Inserts into the @tree table variable the row of the employee whose employee ID was provided to the function as an argument.
- Forms a loop that iterates through all of the direct subordinates of the employee provided to the function as an argument.
- Inserts each direct subordinate’s subtree into the @tree table variable.
Note that the last step is actually a recursive call to the ufn_GetSubtree function. Listing 1 shows the code for the recursive ufn_GetSubtree function.