More About Common Table Expressions in SQL Server 2005 - Displaying the depth of recursion when using recursive CTE: explanation
(Page 4 of 5 )
This section explains the script provided in previous section. Let me explain it part by part. Let us first consider the following code fragment:
SELECT
EmployeeID, ManagerID,0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID is Null
It is not surprising that it retrieves the list of all employees who are not working under any manager. Let us add a very funny structure to the SELECT statement, just like the previous example, which ends up as follows:
WITH EmpCTE
AS
(
SELECT
EmployeeID, ManagerID,0 AS EmployeeLevel
FROM HumanResources.Employee
WHERE ManagerID is Null
Every row retrieved by the SELECT statement will be treated as part of the temporary table named “EmpCTE” (very similar to the first example). Let us proceed with the following statement now:
SELECT
b.EmployeeID, b.ManagerID,EmployeeLevel+1
FROM HumanResources.Employee b
INNER JOIN EmpCTE AS a ON a.EmployeeID = b.ManagerID
The above SELECT statement is considered to be the recursive part (which really enforces the recursion). This statement is similar to the previous example; the only difference we find is with the “EmployeeLevel.” The above statement works with every row present in “EmpCTE.”
It gets executed for every row present in “EmpCTE,” and if it happens to give any output of rows, those rows will again be added to “EmpCTE” and again call itself for every newly added row.
This process gets executed recursively for every new row added (or being added) to the same “EmpCTE.” Finally we retrieve all the information from “EmpCTE” using the following statement:
SELECT EmployeeID,ManagerID,EmployeeLevel
FROM EmpCTE
Order By EmployeeLevel
Next: A recursive common table expression using views: example >>
More MS SQL Server Articles
More By Jagadish Chaterjee
