The Technology Aces blog is a consolidation of the personal blog posts from the individuals that help make Technology Aces the truly elite IT professionals our clients have come to recognize and depend upon.
For more information about the individual authors please follow the links to their blog. Links can be found in the post details section of each post or in the sidebar navigation located on the left side of this page.
Common table expressions (CTEs) provide the significant advantage of being able to reference themselves, making recursion far simpler then earlier versions of SQL Server. CTEs consist of three parts: a name, an optional column list and a query. A recursive query also consists of three parts: an anchor member, a recursive member and a termination check. In the case of recursive CTEs, the termination check is implicit; recursion stops when no rows are returned from the previous invocation. This leaves only anchor member and recursive member for further discussion. The anchor member is relatively simple, this is your base query; think starting point; hierarchy level 1. The recursive member is a bit more tricky. I try to first compose a query which would return hierarchy level 2. Once I am happy with the hierarchy level 1 query (the anchor member) and the hierarchy level 2 query (what will become the recursive member), I construct the CTE.
A CTE must either be the first command executed or follow a command that is terminated with ";". So I have just become accustom to prefixing the CTE declaration with ";":
1: ; WITH CTE_NAME [ ( COLUMN_NAME [,...n] ) ] AS (
2:
3: HIERARCHY_LEVEL_1_QUERY
4:
5: UNION ALL
6:
7: HIERARCHY_LEVEL_2_QUERY
8: INNER JOIN CTE_NAME
9: ON COLUMN_NAME = CTE_NAME.COLUMN_NAME
10: )
11: SELECT *
12: FROM CTE_NAME
The INNER JOIN statement should be the relationship of PARENT = CURRENT (the CTE is always the current hierarchy level) and the UNION ALL in effect iterates through all of the CHILDREN (the remainder of the hierarchy levels).