traversing recursive CTE to the root in SQL Server reaches maximum recursion

Question

I have a typical set of employee and the corresponding manager as most recursive CTE tutorials use. I took it from Uri Dimant's answer in tutorial.

Unlike depth-first search, my goal is to input an employee ID, and the query returns the list of managers up to the root.
So, I tweak the JOIN statement to join manager id from CTE to employee ID. It should get the manager's names for a certain employee.

It results an error:
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

I thought that when the recursion reaches the highest rank of the manager, it would return an empty resultset indicating the end of the recursion.

I want to understand how the SQL engine knows when to stop. and how to make this query works as I expected.

thank you

IF OBJECT_ID('Employees') IS NULL
BEGIN
    CREATE TABLE Employees
    (
    empid   int         NOT NULL,
    mgrid   int         NULL,
    empname nvarchar(25) NOT NULL,
    salary  money       NOT NULL,
    CONSTRAINT PK_Employees PRIMARY KEY(empid),
    CONSTRAINT FK_Employees_mgrid_empid
      FOREIGN KEY(mgrid)
      REFERENCES Employees(empid)
    )


    CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
    SET NOCOUNT ON


    INSERT INTO Employees VALUES(1 , NULL, 'Nancy'   , $10000.00)
    INSERT INTO Employees VALUES(2 , 1   , 'Andrew'  , $5000.00)
    INSERT INTO Employees VALUES(3 , 1   , 'Janet'   , $5000.00)
    INSERT INTO Employees VALUES(4 , 1   , 'Margaret', $5000.00) 
    INSERT INTO Employees VALUES(5 , 2   , 'Steven'  , $2500.00)
    INSERT INTO Employees VALUES(6 , 2   , 'Michael' , $2500.00)
    INSERT INTO Employees VALUES(7 , 3   , 'Robert'  , $2500.00)
    INSERT INTO Employees VALUES(8 , 3   , 'Laura'   , $2500.00)
    INSERT INTO Employees VALUES(9 , 3   , 'Ann'     , $2500.00)
    INSERT INTO Employees VALUES(10, 4   , 'Ina'     , $2500.00)
    INSERT INTO Employees VALUES(11, 7   , 'David'   , $2000.00)
    INSERT INTO Employees VALUES(12, 7   , 'Ron'     , $2000.00)
    INSERT INTO Employees VALUES(13, 7   , 'Dan'     , $2000.00)
    INSERT INTO Employees VALUES(14, 11  , 'James'   , $1500.00)

END
GO

WITH EmpCTE
AS
( 

  -- Anchor Member (AM)
  SELECT 
      empid, 
      empname, 
      mgrid, 
      0 AS level                            -- <------------------- SET LVL START FROM 0
  
  FROM Employees
  WHERE EMPID = 7

  UNION ALL

  -- Recursive Member (RM)
  SELECT 
      e.empid, 
      e.empname, 
      e.mgrid, 
      e.level+1                     -- <------------------- INCREMENT LVL

manager id
  FROM Employees AS m
    JOIN EmpCTE AS e                    -- <------------------- RECURSIVELY CALL EmpCTE
      ON e.mgrid = m.empid
)

SELECT * FROM EmpCTE 

Answer 1

You've managed to create an infinite loop. You can stick in a filter against level to debug these:

(also after removing the manager id)

WITH EmpCTE
AS
( 
  -- Anchor Member (AM)
  SELECT 
      empid, 
      empname, 
      mgrid, 
      0 AS level                            -- <------------------- SET LVL START FROM 0
  FROM Employees
  WHERE EMPID = 7
  UNION ALL
  -- Recursive Member (RM)
  SELECT 
      e.empid, 
      e.empname, 
      e.mgrid, 
      e.level+1                     -- <------------------- INCREMENT LVL
  FROM Employees AS m
    JOIN EmpCTE AS e                    -- <------------------- RECURSIVELY CALL EmpCTE
      ON e.mgrid = m.empid
where level < 2
)
SELECT * FROM EmpCTE;

empid       empname                   mgrid       level
----------- ------------------------- ----------- -----------
7           Robert                    3           0
7           Robert                    3           1
7           Robert                    3           2

This is because you are projecting the columns from EmpCTE as e rather than Employees as m, so you're just getting the same data again and again (plus the level being increased).

WITH EmpCTE
AS
( 
  -- Anchor Member (AM)
  SELECT 
      empid, 
      empname, 
      mgrid, 
      0 AS level                            -- <------------------- SET LVL START FROM 0
  FROM Employees
  WHERE EMPID = 7
  UNION ALL
  -- Recursive Member (RM)
  SELECT 
      m.empid,   -- these columns need to come from m
      m.empname, -- these columns need to come from m
      m.mgrid,   -- these columns need to come from m
      e.level+1                     -- <------------------- INCREMENT LVL
  FROM Employees AS m
    JOIN EmpCTE AS e                    -- <------------------- RECURSIVELY CALL EmpCTE
      ON e.mgrid = m.empid
)
SELECT * FROM EmpCTE;

empid       empname                   mgrid       level
----------- ------------------------- ----------- -----------
7           Robert                    3           0
3           Janet                     1           1
1           Nancy                     NULL        2