The maximum recursion 100 has been exhausted before statement completion (SQL Server)

Question

In SQL Server, I have this simplified table and I'm trying to get a list of all employees with their domain manager:

IF OBJECT_ID('tempdb.dbo.#employees') IS NOT NULL DROP TABLE #employees

CREATE TABLE #employees (
    empid int,
    empname varchar(50),
    mgrid int,
    func varchar(50)
)

INSERT INTO #employees VALUES(1, 'Jeff', 2, 'Designer')
INSERT INTO #employees VALUES(2, 'Luke', 4, 'Head of designers')
INSERT INTO #employees VALUES(3, 'Vera', 2, 'Designer')
INSERT INTO #employees VALUES(4, 'Peter', 5, 'Domain Manager')
INSERT INTO #employees VALUES(5, 'Olivia', NULL, 'CEO')
;

WITH Emp_CTE AS (
SELECT empid, empname, func, mgrid AS dommgr
    FROM #employees

UNION ALL

SELECT e.empid, e.empname, e.func, e.mgrid AS dommgr
    FROM #employees e
    INNER JOIN Emp_CTE ecte ON ecte.empid = e.mgrid
    WHERE ecte.func <> 'Domain Manager'
)

SELECT * FROM Emp_CTE

So the output I want is:

empid   empname func                dommgr
1       Jeff    Designer            4
2       Luke    Head of designers   4
3       Vera    Designer            4

Instead I get this error:
Msg 530, Level 16, State 1, Line 17 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

What am I doing wrong? Is it actually possible with CTE?

Edit: There was indeed an error in the data, the error has gone now, but the result isn't what I want:

empid   empname func                dommgr
1       Jeff    Designer            2
2       Luke    Head of designers   4
3       Vera    Designer            2
4       Peter   Domain Manager      5
5       Olivia  CEO                 NULL
4       Peter   Domain Manager      5
1       Jeff    Designer            2
3       Vera    Designer            2

Possible duplicate of [The maximum recursion 100 has been exhausted before statement completion](https://stackoverflow.com/questions/9650045/the-maximum-recursion-100-has-been-exhausted-before-statement-completion) — David Brabant, Sep 22 '17 at 08:17
Not a duplicate because this is logical error. But i don't understand the logic. Why `mgrid`/`dommgr` is 4 in all of your result records but those records have a different managerid in the table? Could you explain in words what you are trying to achieve? If your root query in the recursive CTE would contain a filter like `WHERE mgrid IS NULL`, then you would avoid infinite recursion, but the result would still be different. — Tim Schmelter, Sep 22 '17 at 08:18
The domain manager's id is `4` but no employees have 4 as `mgrid`, that's why you don't get the result you expect. No ones manager is the `Domain Manager` — Tim Schmelter, Sep 22 '17 at 08:22
The fix to such issues is to *not* use recursion in the first place. It's a lot faster to use a `hierarchyid` column in the first place. — Panagiotis Kanavos, Sep 22 '17 at 08:24
@Tim Schmelter: I want for each employee his domain manager, no matter how many levels are between the employee and the domain manager. Maybe it's just impossible? — SBF, Sep 22 '17 at 08:24
@SBF your data is wrong. Luke and Vera seem to be each other's manager. This results in an infinite recursion — Panagiotis Kanavos, Sep 22 '17 at 08:25
@SBF: not impossible, but your sample data is bad because the corect result would be empty — Tim Schmelter, Sep 22 '17 at 08:26
@PanagiotisKanavos: that's not the only problem but that his root is the whole table without filter. You need an entry point for the recursive cte. In this case its Olivia because she is the CTO without a managerid above her. If Luke got 4 as managerid there was a way down from olivia to jeff and vera. A recursive cte doesn't work if there are gaps — Tim Schmelter, Sep 22 '17 at 08:28

Tim Schmelter · Accepted Answer · 2017-09-22T09:13:35.150

You had two employees which were referenecing each other in the managerid, so one was the manager of the other. That caused the infinite recursion. There was also a gap in the recursion tree because the domain-manager was not referenced anywhere. You have fixed the sample data by changing Luke`s mgrid to 4. Now there is no gap and no lgical issue anymore. But you also had no root entry for the recursion, the first query has no filter.

You can use this query:

WITH DomainManager AS (
SELECT empid, empname, func, dommgr = empid, Hyrarchy = 1
    FROM #employees
    WHERE func = 'Domain Manager'

UNION ALL

SELECT e.empid, e.empname, e.func, dommgr, Hyrarchy = Hyrarchy +1
    FROM #employees e
    INNER JOIN DomainManager dm ON dm.empid = e.mgrid
)

SELECT * FROM DomainManager
WHERE func <> 'Domain Manager'
ORDER BY empid

Note that the enry/root point for the CTE is the Domain Manager because you want to find every employees domain manager's ids. This id is transported down the hyrarchy. The final select needs to filter out the Domain Manager because you only want his ID for every employee, you dont want to include him in the result set.

The result of the query is:

empid   empname func                dommgr   Hyrarchy
1       Jeff    Designer               4       3
2       Luke    Head of designers      4       2
3       Vera    Designer               4       3

This is so perfect! Thanks and sorry for the confusion with the data. — SBF, Sep 22 '17 at 09:07

score 0 · Answer 2 · answered Sep 22 '17 at 09:09

The error message is raised because the data contains a circular reference between Luke and Vera.

It's easier to perform hierarchical queries if you add a hierarchyid field. SQL Server provides functions that return descendants, ancestors and the level in a hierarchy. hierarchyid fields can be indexed resulting in improved performance.

In the employee example, you can add a level field :

declare @employees table (
    empid int PRIMARY KEY,
    empname varchar(50),
    mgrid int,
    func varchar(50),
    level hierarchyid not null,
    INDEX IX_Level (level)
)

INSERT INTO @employees VALUES
(1, 'Jeff', 2, 'Designer'         ,'/5/4/2/1/'),
(2, 'Luke', 4, 'Head of designers','/5/4/2/'),
(3, 'Vera', 2, 'Designer'         ,'/5/4/2/3/'),
(4, 'Peter', 5, 'Domain Manager'  ,'/5/4/'),
(5, 'Olivia', NULL, 'CEO'         ,'/5/')
;

` declare @employees table ( empid int PRIMARY KEY, empname varchar(50), mgrid int, func varchar(50), level hierarchyid not null, INDEX IX_Level (level) )

INSERT INTO @employees VALUES
(1, 'Jeff', 2, 'Designer'         ,'/5/4/2/1/'),
(2, 'Luke', 4, 'Head of designers','/5/4/2/'),
(3, 'Vera', 2, 'Designer'         ,'/5/4/2/3/'),
(4, 'Peter', 5, 'Domain Manager'  ,'/5/4/'),
(5, 'Olivia', NULL, 'CEO'         ,'/5/')
;

/5/4/2/1/ is the string representation of a hieararchyID value. It's essentially the path in the hierarchy that leads to a particular row.

To find all subordinates of domain managers, excluding the managers themselves, you can write :

with DMs as 
(
    select EmpID,level 
    from @employees
    where func='Domain Manager'
)
select 
    PCs.empid,
    PCs.empname as Name,
    PCs.func as Class,
    DMs.empid as DM,
    PCs.level.GetLevel() as THAC0,
    PCs.level.GetLevel()- DMs.level.GetLevel() as NextLevel
from 
    @employees PCs 
    inner join DMs on PCs.level.IsDescendantOf(DMs.level)=1
where DMs.EmpID<>PCs.empid;

The CTE is only used for convenience

The result is :

empid   Name    Class               DM  THAC0   NextLevel
1       Jeff    Designer            4   4       2
2       Luke    Head of designers   4   3       1
3       Vera    Designer            4   4       2

The CTE returns all DMs and their hierarchyid value. The IsDescendantOf() query checks whether a row is a descentant of a DM or not. GetLevel() returns the level of the row in the hierarchy. By subtracting the DM's level from the employee's we get the distance between them

This would involve a change to the table structure or view and that's not possible in my case — SBF, Sep 22 '17 at 09:22

score 0 · Answer 3 · answered Sep 22 '17 at 09:17

Like others said, you have here a problem with data (Vera).

IF OBJECT_ID('tempdb.dbo.#employees') IS NOT NULL 
    DROP TABLE #employees

CREATE TABLE #employees (
    empid int,
    empname varchar(50),
    mgrid int,
    func varchar(50)
)

INSERT INTO #employees VALUES(1, 'Jeff', 2, 'Designer')
INSERT INTO #employees VALUES(2, 'Luke', 3, 'Head of designers')
INSERT INTO #employees VALUES(3, 'Vera', 4, 'Designer')         --**mgrid = 4 instead 2**
INSERT INTO #employees VALUES(4, 'Peter', 5, 'Domain Manager')
INSERT INTO #employees VALUES(5, 'Olivia', NULL, 'CEO')

;WITH Emp_CTE AS 
(
    SELECT empid, empname, func, mgrid AS dommgr, 0 AS Done
    FROM #employees
    UNION ALL
    SELECT ecte.empid, ecte.empname, ecte.func, 
        CASE WHEN e.func = 'Domain Manager' THEN e.empid ELSE e.mgrid END AS dommgr, 
        CASE WHEN e.func = 'Domain Manager' THEN 1 ELSE 0 END AS Done
    FROM Emp_CTE AS ecte
        INNER JOIN #employees AS e ON 
            ecte.dommgr = e.empid
    WHERE ecte.Done = 0--emp.func <> 'Domain Manager'
)
SELECT * 
FROM Emp_CTE
WHERE Done = 1

The maximum recursion 100 has been exhausted before statement completion (SQL Server)

3 Answers3