Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Answer to Recursion Challenge
Here's the answer to the recursion challenge that SQL Server MVP Itzik Ben-Gan presented in the T-SQL Black Belt article "Manipulating Hierarchies with UDFs."
Itzik Ben-Gan
December 19, 2000
1 Min Read
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.
About the Author
You May Also Like
.png?width=700&auto=webp&quality=80&disable=upscale)