Recursive Queries using Common Table Expressions (CTE) in SQL Server

Recursive Queries using Common Table Expressions (CTE) in SQL Server

WITH cte_alias (column_aliases)  AS  ( cte_query_definition   --initialization UNION ALL cte_query_definition2 --recursive execution )  SELECT * FROM cte_alias


WITH Managers AS ( --initialization SELECT EmployeeID, LastName, ReportsTo  FROM Employees WHERE ReportsTo IS NULL UNION ALL --recursive execution SELECT e.employeeID,e.LastName, e.ReportsTo FROM Employees e INNER JOIN Managers m  ON e.ReportsTo = m.employeeID ) SELECT * FROM Managers

SELECT * FROM Managers OPTION (MAXRECURSION 4)



DECLARE @rowsAdded INT

--table variable to hold accumulated results
DECLARE @managers TABLE --initialize @managers who do not have managers
   (EmpID INT, MgrID INT, processed INT DEFAULT(0))

INSERT @managers
SELECT EmployeeID, ReportsTo, 0
FROM Employees
WHERE ReportsTo IS NULL

SET @rowsAdded=@@rowcount

--do this while new employees are added in the previous iteration
WHILE @rowsAdded > 0
BEGIN

   --mark employee records going to be found in this iteration with --processed=1
   UPDATE @managers SET processed=1 WHERE processed=0

   --insert employees who report to employees not yet processed
   INSERT @managers
   SELECT EmployeeID, ReportsTo, 0
   FROM Employees e
   INNER JOIN @managers r ON e.ReportsTo = r.EmpID
   WHERE ReportsTo <> EmployeeID AND r.processed = 1
        
   SET @rowsAdded = @@rowcount

   --mark employee records found in this iteration as processed
   UPDATE @managers SET processed=2 WHERE processed=1

END

SELECT * FROM @managers