Why do I find all the examples of how to use recursion in SQL Server 2005 too complicated (including the one in the Microsoft help)?
Here is mine that does the bare minimum. First, let’s create a table and populate it with hierarchical test data:
The recursion uses a Common Table Expression (CTE) with a union clause. The first part of the Union returns the root nodes (multiple roots are possible). In the first iteration, the second part returns all the children of the root, then all the children of those, etc. SQL Server handles a recursion depth of up to 100.
By joining to the parent record, the 2nd part of the union can do cool things like tracking the recursion depth (in the Level column) or concatenating all ids in the path to the current element (in the Path column). In fact, the string concatenation is the most complicated part of this query. Here is the output:
Very convenient …