Use OPTION (MAXRECURSION 0)

Question

I have the following sql query that is run on a hierarchical data:

WITH DirectReports(RootId, ManagerId, Id, RelativeEmployeeLevel) AS
(
    SELECT Id RootId, ManagerId, Id, 0 AS RelativeEmployeeLevel
    FROM tbl    
    UNION ALL
    SELECT d.RootId, e.ManagerId, e.Id, RelativeEmployeeLevel + 1
    FROM tbl AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerId = d.Id   
), q2 as
(
    SELECT RootId,
           ManagerId,
           Id,           
           RelativeEmployeeLevel,
           max(RelativeEmployeeLevel) over (partition by RootId) - RelativeEmployeeLevel LevelsBelow
    FROM DirectReports  
)

SELECT * FROM q2 where rootid = Id

On running this, I see this error: 'The statement terminated. The maximum recursion 100 has been exhausted before statement completion'

I updated the query to include:

OPTION (MAXRECURSION 0)

WITH DirectReports(RootId, ManagerId, Id, RelativeEmployeeLevel) AS
(
    SELECT Id RootId, ManagerId, Id, 0 AS RelativeEmployeeLevel
    FROM tbl    
    UNION ALL
    SELECT d.RootId, e.ManagerId, e.Id, RelativeEmployeeLevel + 1
    FROM tbl AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerId = d.Id   
), q2 as
(
    SELECT RootId,
           ManagerId,
           Id,           
           RelativeEmployeeLevel,
           max(RelativeEmployeeLevel) over (partition by RootId) - RelativeEmployeeLevel LevelsBelow
    FROM DirectReports  
    OPTION (MAXRECURSION 0)
)

SELECT * FROM q2 where rootid = Id

I see an error: Incorrect syntax near the keyword 'OPTION'

What am I missing?

You got more 100 levels of managers? That doesn't sound right. — HoneyBadger, Sep 15 '22 at 19:29
@squillman like this SELECT * FROM q2 where rootid = Id OPTION (MAXRECURSION 0) ? — user989988, Sep 15 '22 at 19:50
If you get the same error, you didn't put it in the right place, @user989988 . — Thom A, Sep 15 '22 at 21:04

score 0 · Answer 1 · answered Sep 15 '22 at 21:08

0

You're trying to use a recursive CTE to return a top-down view of the organization starting with every employee. I doubt every employee is at the top of the organization. If they were, there would be no reason to do this. My guess is that there is one person at the top.

Try this first without OPTION(MAXRECURSION = 0). I can't imagine you have over 100 layers of management.

WITH DirectReports(RootId, ManagerId, Id, RelativeEmployeeLevel) AS
(
    SELECT Id RootId, ManagerId, Id, 0 AS RelativeEmployeeLevel
    FROM tbl
WHERE EmployeeName = 'Head Honcho'
    UNION ALL
    SELECT d.RootId, e.ManagerId, e.Id, RelativeEmployeeLevel + 1
    FROM tbl AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerId = d.Id   
), q2 as
(
    SELECT RootId,
           ManagerId,
           Id,           
           RelativeEmployeeLevel,
           max(RelativeEmployeeLevel) over (partition by RootId) - RelativeEmployeeLevel LevelsBelow
    FROM DirectReports  
)

SELECT * FROM q2 where rootid = Id

answered Sep 15 '22 at 21:08

dougp

2,810
1
8
31

This is the first query the OP has, and they say it errors out due to maxrecursion. I suspect, as I stated in the comments, due to a circular reference. – Thom A Sep 15 '22 at 21:36
It is not the first query the OP has. This one has a `WHERE` clause. – dougp Sep 15 '22 at 21:56
If there's a circular reference it's because the data is messed up. The query looks fine except that it assumes every employee is the CEO. – dougp Sep 15 '22 at 21:57
I'd bet it's something like the top boss's boss is the top boss. In other words Id=0 and ManagerId=0. `OPTION(MAXRECURSION=0)` would cause the query to loop until the server ran out of memory. I'm thinking that may be bad. It should be ManagerId=NULL – dougp Sep 16 '22 at 15:08

Use OPTION (MAXRECURSION 0)

1 Answers1