title: SQL - How to avoid maximum recursion in a recursive query using CTE
body: <p>I have a table with Users and their managers:</p>
  
  <pre><code>|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      |
  </code></pre>
  
  <p>I have a simple recursive query that returns all managers IDs in the hierarchy starting with the given top manager ID and below:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:
  <a href="https://i.stack.imgur.com/0QWCT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0QWCT.png" alt="enter image description here"></a></p>
  
  <p>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.</p>
  
  <p>How can I do that?</p>
  
  <p>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.</p>
  
  <p>How do I step out of the recursion and continue executing a script without any errors?</p>