Microsoft introduced the Common Table Expression (CTE) with the release of SQL Server 2005, so it has been around for quite some time and I have been using CTE’s since I started using SQL Server 2005 however one of the little benefits it does give you is the ability to perform a recursive statement. Up until the other day I had not needed or even thought about performing a recursive statement. When on a client site they came to me with a request to create a stored procedure that they could use to display some parent child relationships.
The result was to use a Recursive CTE in the stored procedure having a parameter passed in as the starting Parent record.
Recursive CTE’s can be used in this type of situation to easily show the Parent / Child relationships and the varying levels in the hierarchy.
I will run you through the simple steps I took to be able to achieve this Hierarchy result set. First off for this blog I needed to create a temporary table that contains some parent/child data.
Now that we have the table created and populated we can start to look at our recursive CTE statement to display the parent and it’s associate child records. Now in this we are going to show the Child’s child records as well giving us a Grandparent, Parent, Child Hierarchy result set.
As you can see from the statement above I have declared a Variable that allows us to use for the Parent record we are looking for. The first Select Statement in the CTE is giving us the root or parent level in the result set. This can be seen in the results below by having the Hierarchy level of 0. The Union All statement is required to return all results. The Second statement is joining the table back on the CTE giving us the Recursive nature of the statement joining the ParentID from the table to the ChildItem from the CTE and Incrementing the Parent Hierarchy Level by 1.
Result Set 1 Shows us the full set of results having levels 0 – 2
A change in the ParentID to 100 gives us the following results
We can see from the 2 result sets, that the first has 3 levels where the second only has 2 levels.
I hope this has made it easy for you to tackle your own requirements for a Hierarchical result set.
Leave a Reply