Last Friday evening, I forced to look into a procedure which is using Recursive Common Table (CTE) . The procedure was not returning the result.I was not very comfortable with the recursive CTE as I am not able to visualize the execution of recursive CTE. After struggling for an hour , we managed to fix the issue. Then I thought it will be good to learn how the recursive CTE works. In this post let us see how recursive CTE works.
First of all let us create a table called Employee table using the below script.
CREATE TABLEEmployee
(Employee_id INT PRIMARY KEY,
EmployeeName VARCHAR(100),
Manager_id INT
)
Let us populate the sample data.
INSERT INTO Employee VALUES(1,'A',NULL)INSERT INTO EmployeeVALUES (2,'A_B',1)
INSERT INTOEmployee VALUES (3,'A_C',1)INSERT INTO EmployeeVALUES (4,'A_D',1)
INSERT INTOEmployee VALUES (5,'B_B',2)INSERT INTO EmployeeVALUES (6,'B_C',2)
INSERT INTOEmployee VALUES (7,'C_B',3)INSERT INTO EmployeeVALUES (8,'C_C',3)
INSERT INTOEmployee VALUES (9,'BB_B',5)INSERT INTO Employee VALUES(10,'BB_C',5)
INSERT INTO EmployeeVALUES (11,'BC_B',6)
INSERT INTOEmployee VALUES (12,'BC_C',6)
INSERT INTO EmployeeVALUES (13,'BBB_B',9)
INSERT INTOEmployee VALUES (14,'BBC_B',10) I have used separate insert statement for better readability.
Here Manager_id is a foreign key referring to Employee_id. Let us assume that we need to generate an employee reports with Employee_id,Employee Name ,Manager_id,Manager Name and hierarchical position (level) of employee in the organization. This can be implemented very easily using recursive CTE which introduced in SQL server 2005.Below script will give the result.
;WITH DirectReports(Manager_ID, ManagerName,Employee_ID, EmployeeName ,EmployeeLevel)AS (
--Select the root or parentrecords
SELECT Manager_ID, CAST('' AS VARCHAR(100))AS ManagerName, Employee_ID, EmployeeName, 0 AS EmployeeLevel
FROMEmployee
WHERE Manager_IDIS NULL
UNION
ALL
--Recursive part :Select the child
SELECT e.Manager_ID, m.EmployeeName ASManagerName, e.Employee_ID, e.EmployeeName, EmployeeLevel +1
FROMEmployee e
INNER JOIN DirectReportsd
ON e.Manager_ID =
d.Employee_ID
INNER JOIN employee m
ON e.manager_ID =
m.employee_id
)
SELECT * FROM DirectReports ;
Let us see how we can write the same with out CTE, which will help us to visualize the recursive CTE.
DECLARE @DirectReports AS TABLE
(Manager_ID INT,
ManagerName VARCHAR(100),
Employee_ID INT,
EmployeeName VARCHAR(100) ,
EmployeeLevel INT)--Selecting the 0th level (who do not have manager)employee
INSERT INTO @DirectReports
SELECT Manager_ID,
CAST('' AS VARCHAR(100))AS ManagerName,
Employee_ID, EmployeeName ,
0 AS EmployeeLevel
FROMEmployee
WHERE Manager_IDIS NULL
DECLARE@Recursion INT =0
WHILE(@@ROWCOUNT>0)BEGIN
SET
@Recursion =@Recursion +1
INSERT INTO @DirectReports
SELECT
e.Manager_ID,
m.EmployeeName AS ManagerName,
e.Employee_ID,
e.EmployeeName ,
@Recursion
FROM Employee e
INNER JOIN @DirectReports d
ONe.Manager_ID = d.Employee_ID INNER JOIN employee m ON e.manager_ID =
m.employee_id
WHERE d.EmployeeLevel=@Recursion -1 --to select only the last execution result
END
SELECT * FROM @DirectReports
Hope this will help us to write/troubleshoot the recursive CTE in much comfortable way.
If you liked this post, do like my page on FaceBook
www.PracticalSqlDba.com
