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
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