3

I have a table with Users and their managers:

|ID  | Title     | Manager |
|1   | Manager 1 | 4       |
|2   | Manager 2 | 1       |
|3   | Manager 3 | 1       |
|4   | Manager 4 | 2       |
|5   | Manager 5 | 3       |
...
|10  | Manager 10| NULL    |
|11  | Manager 11| 10      |

I have a simple recursive query that returns all managers IDs in the hierarchy starting with the given top manager ID and below:

DECLARE @Managers TABLE (ManagerID int)
DECLARE @ManagerID int = 1
        BEGIN
            ;WITH ManagerCTE AS (
                                SELECT ID FROM tblUsers WHERE ID = @ManagerID
                                UNION ALL
                                SELECT chld.ID FROM tblUsers chld 
                                INNER JOIN ManagerCTE items ON chld.Manager = items.ID
                                )
            INSERT INTO @Managers
            SELECT ID FROM ManagerCTE
        END
SELECT * FROM @Managers

And it works fine if the managers hierarchy structure is well organized. But on some instances we have disorganized structure, where the lower manager happens to be the manager of the upper manager: enter image description here

In this case the recursive query goes into a loop and maximum recursion 100 is being exhausted before statement completion. I need to exclude those managers from the query if they are alredy selected into the resulting table so as to avoid these loops.

How can I do that?

The other possible solution is to just step out from recursion when reaching some level or it, for example, 5. But option (maxrecursion 5) is just sets the limit, and the query produces an error if the limit is reached.

How do I step out of the recursion and continue executing a script without any errors?

Improve this question
9
  • If your concern is about the limit 100 you can give max limit by from ManagerCTE option (maxrecursion 0) Commented Feb 10, 2017 at 12:39
  • Shouldn't this be fixed in the database? Seems to me to be a situation that cannot (or at least shouldn't) exist. Commented Feb 10, 2017 at 12:45
  • This won't be fixed if I put (maxrecursion 0), because we will get an endless loop. Commented Feb 10, 2017 at 12:47
  • You get an endless loop because it is an endless loop. To fix it, you'll have to break the loop in the database. Commented Feb 10, 2017 at 12:52
  • 2
    In this answer I use a growing path string to kinda store the visited nodes. Check how I use LIKE '%| ' + nxt.Id + '%' to break the recursion in case of re-visiting a node. But - to be honest - this is cutting a bread with a chain saw: SQL-Server is the wrong tool for this! Commented Feb 10, 2017 at 13:16

2 Answers 2

Reset to default
3

As told in my comments you can either check the visited nodes as I do it here by storing them in a growing string path, or you can limit the recursion's depth with a recursive CTE like this:

SELECT 1 AS CurrentLevel,ID FROM tblUsers WHERE ID = @ManagerID
UNION ALL
SELECT items.CurrentLevel+1,chld.ID FROM tblUsers chld 
INNER JOIN ManagerCTE items ON chld.Manager = items.ID
WHERE items.CurrentLevel<=5
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, I will probably just limit the recursion's depth.
0

Try this within WITH:

SELECT ID, ID::text as ids FROM tblUsers WHERE ID = @ManagerID
UNION ALL
SELECT chld.ID FROM tblUsers chld, items.ids || ',' || chld.ID::text as ids
INNER JOIN ManagerCTE items ON chld.Manager = items.ID AND chld.ID::text not like '%'||items.ids||'%'

Code is written under postgresql. Change string's functions according to yours database.

Improve this answer

5 Comments

That wouldn't work, because i receive an error: "Recursive member of a common table expression 'ManagerCTE' has multiple recursive references"
Which database do you use?
SQL Server 2012, but the query should work fine on newer databases as well.
Look at the changed version. Sorry, but I have not access to MS SQL Server, so SQL is written for Postgresql, but you can see the idea
This doesn't work on SQL Server 2012, but I will probably just limit the recursion's depth, as suggested above. Thanks for being ready to help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.