Pages

Monday 6 August 2012

SQL Server: How Recursive Common Table Expression (CTE) Works ?

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 TABLE Employee
(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 Employee VALUES (2,'A_B',1)
INSERT INTO Employee VALUES (3,'A_C',1)
INSERT INTO Employee VALUES (4,'A_D',1)

INSERT INTO Employee VALUES (5,'B_B',2)
INSERT INTO Employee VALUES (6,'B_C',2)

INSERT INTO Employee VALUES (7,'C_B',3)
INSERT INTO Employee VALUES (8,'C_C',3)

INSERT INTO Employee VALUES (9,'BB_B',5)
INSERT INTO Employee  VALUES (10,'BB_C',5)

INSERT INTO Employee VALUES (11,'BC_B',6)
INSERT INTO Employee VALUES (12,'BC_C',6)

INSERT INTO Employee VALUES (13,'BBB_B',9)
INSERT INTO Employee 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 parent records
    
SELECT 
         Manager_ID,
         CAST('' AS VARCHAR(100))AS ManagerName
         Employee_ID
         EmployeeName,
         0 AS EmployeeLevel
    FROM Employee
    WHERE Manager_ID IS NULL

    
UNION ALL
   
 --Recursive part :Select the child
 
SELECT 
     e.Manager_ID,
     m.EmployeeName AS ManagerName
     e.Employee_ID
     e.EmployeeName,
     EmployeeLevel + 1
 FROM Employee e
        INNER JOIN DirectReports d
        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
    FROM Employee
    WHERE Manager_ID IS 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
        ON e.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

9 comments:

  1. while creating CTE it showing error message..

    Msg 102, Level 15, State 1, Line 8
    Incorrect syntax near ')'.

    Please give some solution..

    ReplyDelete
  2. Got solution..Thanks...
    SELECT
    e.Manager_ID,
    m.EmployeeName AS ManagerName,
    e.Employee_ID,
    e.EmployeeName,
    EmployeeLevel + 1
    FROM Employee e
    INNER JOIN DirectReports d
    ON e.Manager_ID = d.Employee_ID
    INNER JOIN employee m ON e.manager_ID = m.employee_id
    )
    SELECT * FROM DirectReports ;

    it should excuted once..
    Thanks for Solution.

    ReplyDelete
  3. Hi Nelson,

    thanks for your example about recursive cte. I also posted about the same subject:

    http://www.dotmaniac.net/recursive-ctes-for-dummies/

    Regards!

    ReplyDelete
  4. Hi Nelson,

    Nice example and way to explain... 2nd example (without using cte) is very good to understand how CTE works in hierarchical manner. Now I understand, how CTE works. In the manner of recursion, it consider only previous result set to join in its Recursive part. Thank you so much.

    Regards,
    Lokesh

    ReplyDelete
  5. instead of just 1 level below, if we wanted to list everyone below a person, how would we modify this?

    ReplyDelete
  6. Thanks, nice explanation

    ReplyDelete
  7. كما تقوم المؤسسة على إدخار أسعار مناسبة لجميع الزبائن كما تكفل التنوع في تأدية الخدمة وتعتمد ايضا على متخصصون متخصصين في ميدان تطهير الموكيت والمفروشات بمختلف أشكالها.
    شركة النجوم لخدمات التنظيف
    شركة تنظيف كنب بجدة
    شركة تنظيف موكيت بجدة
    شركة تسليك مجاري بجدة

    ReplyDelete
  8. Great article! Recursive CTEs can be so powerful, but tricky to understand at first. Thanks for breaking it down! best seo services in gwalior

    ReplyDelete