So, what is CTE? It stands for Common Table Expression. It
allows the code to reference itself. This assists us in creating recursive CTE.
The initial CTE is repeatedly executed and it returns subsets of data. This
will continue on until the complete dataset is returned.
CTE Structure
The CTE Structure will look like the following.
Listing 1
With <CTE Name>(Column1, column2,…, column n)
AS
(
<Query Definition>- Anchor Member
Union All
<Query Definition> - Uses the <CTE Name> for recursive member
)
SELECT *
FROM <CTE Name>
There are three elements to recursive CTE.
·
Invocation of the routine: As shown above, the first invocation
is with the anchor query definition. There can be one anchor query definition
or multiple, but all anchor query definitions should be positioned before the
Recursive member definition. The anchor query definitions can be joined using
Union All, Union, Except and Intersect. To join the Recursive Member you need
to use the Union All operator.
·
Recursive Invocation of the routine: This includes one or more
CTE query definitions that reference the CTE itself. The Query definitions
need to be joined by Union All Operator.
·
Termination Check: Recursion stops when no rows are returned from
the previous invocation.
Let us consider
the CTE Structure code shown above. How does the execution really occur in
this scenario?
1.
The CTE Expression is split into anchor query members and recursive
members.
2.
Run the anchor query and create a base result set.
3.
Run the recursive member with the previous output as input and generate
the next output.
4.
Repeat the above step until the result set output returns empty.
5.
Return the complete resultset. This is union of all outputs.
Table structure for hierarchy
Consider the table structure we have for our example. We
are going to a create tree structure for the departments.
tblDepartments
|
DepartmentID
|
DepartmentName
|
ParentDepartmentID
|
The sample data is as follows.
DepartmentID
|
DepartmentName
|
ParentDepartmentID
|
1
|
HR
|
<NULL>
|
2
|
IT
|
<NULL>
|
3
|
Networking
|
2
|
4
|
Development
|
2
|
5
|
ASP.NET Development
|
4
|
6
|
J2EE Development
|
4
|
7
|
C#.NET
|
5
|
8
|
VB.NET
|
5
|