I have below hierarchy details:

INSERT INTO dbo.Employee (EmployeeID, EmployeeName, ManagerID, TeamLeadID)
VALUES
(1, 'M1', NULL, NULL),       -- Root manager (M1)
(2, 'M2', 1, NULL),          -- M2 reports to M1
(3, 'M3', 1, NULL),          -- M3 reports to M1
(4, 'T1', 2, NULL),          -- T1 is a team lead, reports to M2
(5, 'T1_1', 2, 4),           -- T1_1 is a team member reports to M2 and T1
(6, 'T1_2', 2, 4),           -- T1_2 reports to M2 and T1
(7, 'T1_3', NULL, 4),        -- T1_3 reports only to T1 (no manager)
(8, 'T2', 3, NULL),          -- T2 is a team lead, reports to M3
(9, 'T2_1', 3, 8),           -- T2_1 reports to M3 and T2
(10, 'T2_2', 3, 8);          -- T2_2 reports to M3 and T2

I'm trying to fetch the hierarchy details for each and every employee.

For example:

• If Employee ID = M1 is given as parameter, it should display all M1 hierarchy details. i.e M1,M2,M3,T1,T1_1,T1_2,T1_3, T2, T2_1 and T2_2.

• If M2 is given as parameter, then it should display all M2 hierarchy details. i.e M2,T1,T1_1,T1_2 and T1_3

• If T2_2 is given as parameter then it should display only one record i.e T2_1 records as there is no further hierarchy

I'm using a recursive query to fetch the details:

;WITH Hierarchy AS 
(
    SELECT *  
    FROM dbo.Employee  
    WHERE employeeid = 1

    UNION ALL  

    SELECT e.*
    FROM dbo.Employee e 
    INNER JOIN Hierarchy h ON h.employeeid = e.ManagerID  
                           OR h.employeeid = e.TeamLeadID
)  
SELECT *
FROM Hierarchy H
OPTION (MAXRECURSION 10000);

This query is not returning the results as expected and also somehow it is causing issues when we deal with Prod data as it is taking more time to return the results, and it causes this error:

The statement terminated. The maximum recursion 10000 has been exhausted before statement completion.

Please let me know what the best solution to handle this would be.

I have below hierarchy details:

INSERT INTO dbo.Employee (EmployeeID, EmployeeName, ManagerID, TeamLeadID)
VALUES
(1, 'M1', NULL, NULL),       -- Root manager (M1)
(2, 'M2', 1, NULL),          -- M2 reports to M1
(3, 'M3', 1, NULL),          -- M3 reports to M1
(4, 'T1', 2, NULL),          -- T1 is a team lead, reports to M2
(5, 'T1_1', 2, 4),           -- T1_1 is a team member reports to M2 and T1
(6, 'T1_2', 2, 4),           -- T1_2 reports to M2 and T1
(7, 'T1_3', NULL, 4),        -- T1_3 reports only to T1 (no manager)
(8, 'T2', 3, NULL),          -- T2 is a team lead, reports to M3
(9, 'T2_1', 3, 8),           -- T2_1 reports to M3 and T2
(10, 'T2_2', 3, 8);          -- T2_2 reports to M3 and T2

I'm trying to fetch the hierarchy details for each and every employee.

For example:

• If Employee ID = M1 is given as parameter, it should display all M1 hierarchy details. i.e M1,M2,M3,T1,T1_1,T1_2,T1_3, T2, T2_1 and T2_2.

• If M2 is given as parameter, then it should display all M2 hierarchy details. i.e M2,T1,T1_1,T1_2 and T1_3

• If T2_2 is given as parameter then it should display only one record i.e T2_1 records as there is no further hierarchy

I'm using a recursive query to fetch the details:

;WITH Hierarchy AS 
(
    SELECT *  
    FROM dbo.Employee  
    WHERE employeeid = 1

    UNION ALL  

    SELECT e.*
    FROM dbo.Employee e 
    INNER JOIN Hierarchy h ON h.employeeid = e.ManagerID  
                           OR h.employeeid = e.TeamLeadID
)  
SELECT *
FROM Hierarchy H
OPTION (MAXRECURSION 10000);

This query is not returning the results as expected and also somehow it is causing issues when we deal with Prod data as it is taking more time to return the results, and it causes this error:

The statement terminated. The maximum recursion 10000 has been exhausted before statement completion.

Please let me know what the best solution to handle this would be.

• sql
• sql-server

• 4 Do you have a loop in your actual data somewhere? Your problem cannot be reproduced with the sample data given dbfiddle.uk/i-ZFMCaG "This query is not providing the results as expected" what is expected then? Please show as a Markdown Table – Charlieface Commented Mar 17 at 13:39
• Add a loop counter and stop at 5,10,20 loops and see if the result is coming together as expected. – Bart McEndree Commented Mar 17 at 13:44
• 1 Guessing you have a loop somewhere in your hierarchy – siggemannen Commented Mar 17 at 13:45
• 1 Sounds to me like you have some circular references. – Thom A Commented Mar 17 at 13:45
• if Employee ID = M1 but you are coding if employeeid = 1 , also If M2 is given as parameter why is T1_3 in your result given the comment -- T1_3 reports only to T1 (no manager) you seem to jumping hierarchies. – P.Salmon Commented Mar 17 at 13:49

3 Answers 3

You have two issues:

• Multiple paths through the hierarchy to a single row, because you are going via both ManagerID and TeamLeaderID. You can use GROUP BY or DISTINCT to just group it up into one row.
• You seem to have a circular reference (a loop) somewhere in your real data. You need to filter these out by checking the chain as you go.

WITH Hierarchy AS (  
    SELECT
      e.*,
      CAST(CONCAT(',', e.EmployeeID, ',')  AS varchar(8000)) AS chain
    FROM dbo.Employee e
    WHERE e.EmployeeID = 1

    UNION ALL  

    SELECT
      e.*,
      CONCAT(h.chain, e.EmployeeId, ',')
    FROM dbo.Employee e 
    INNER JOIN Hierarchy h ON h.EmployeeID IN (e.ManagerID, e.TeamLeadID)
    WHERE h.chain NOT LIKE CONCAT('%,', e.EmployeeID, ',%')
)  
SELECT
  EmployeeID,
  EmployeeName,
  ManagerID,
  TeamLeadID,
  MIN(chain),
  MAX(chain)
FROM Hierarchy h
GROUP BY
  EmployeeID,
  EmployeeName,
  ManagerID,
  TeamLeadID;

db<>fiddle

The recursive query you are using is correct only need to modify 2 things:

1. Join - COALESCE(e.ManagerID, e.TeamLeadID)

2. Parameter - DECLARE @EMPId INT = 1

here is the query you required:

DECLARE @EMPId INT = 1

;WITH Hierarchy AS 
(
    SELECT *  
    FROM dbo.Employee  
    WHERE employeeid = @EMPId

    UNION ALL  

    SELECT e.*
    FROM dbo.Employee e 
    INNER JOIN Hierarchy h 
        ON h.employeeid = COALESCE(e.ManagerID, e.TeamLeadID)
)  
SELECT *
FROM Hierarchy H
ORDER BY COALESCE(ManagerID, TeamLeadID)
OPTION (MAXRECURSION 1000);

this will give output as per your requirements:

1. If Employee ID = 1 ('M1')

2. If Employee ID = 2 ('M2')

3. If Employee ID = 10 ('T2_2')

You must distinguish between the two hierarchies. If not this is not a tree by a cirsuit driving to infinite loop.

CREATE TABLE dbo.Employee 
(EmployeeID      INT PRIMARY KEY, 
 EmployeeName    VARCHAR(16) NOT NULL UNIQUE, 
 ManagerID       INT REFERENCES dbo.Employee (EmployeeID), 
 TeamLeadID      INT REFERENCES dbo.Employee (EmployeeID));
GO

INSERT INTO dbo.Employee (EmployeeID, EmployeeName, ManagerID, TeamLeadID)
VALUES
(1, 'M1', NULL, NULL),       -- Root manager (M1)
(2, 'M2', 1, NULL),          -- M2 reports to M1
(3, 'M3', 1, NULL),          -- M3 reports to M1
(4, 'T1', 2, NULL),          -- T1 is a team lead, reports to M2
(5, 'T1_1', 2, 4),           -- T1_1 is a team member reports to M2 and T1
(6, 'T1_2', 2, 4),           -- T1_2 reports to M2 and T1
(7, 'T1_3', NULL, 4),        -- T1_3 reports only to T1 (no manager)
(8, 'T2', 3, NULL),          -- T2 is a team lead, reports to M3
(9, 'T2_1', 3, 8),           -- T2_1 reports to M3 and T2
(10, 'T2_2', 3, 8);          -- T2_2 reports to M3 and T2

WITH 
ManagerHierarchy AS 
(
    SELECT *, 'Manager' AS Inherits 
    FROM   dbo.Employee  
    WHERE  ManagerID IS NULL

    UNION ALL  

    SELECT e.*, 'Manager' AS Inherits 
    FROM   dbo.Employee AS e 
           INNER JOIN ManagerHierarchy AS h ON h.EmployeeID = e.ManagerID  
),
TeamLeadHierarchy AS 
(
    SELECT *, 'Team leader' AS Inherits   
    FROM   dbo.Employee  
    WHERE  TeamLeadID IS NULL

    UNION ALL  

    SELECT e.*, 'Team leader' AS Inherits   
    FROM   dbo.Employee AS e 
           INNER JOIN TeamLeadHierarchy AS h ON h.EmployeeID = e.TeamLeadID  
)
SELECT *
FROM   ManagerHierarchy
UNION ALL
SELECT *
FROM TeamLeadHierarchy;