title: Infinite loop CTE with OPTION (maxrecursion 0)
body: <p>I have CTE query with large record on it. Previously it worked fine. But lately, it throws an error for some members </p>
  
  <blockquote>
    <p>The statement terminated. The maximum recursion 100 has been exhausted before statement completion.</p>
  </blockquote>
  
  <p>So I put <code>OPTION (maxrecursion 0)</code> or <code>OPTION (maxrecursion 32767)</code> on my query, because I don't want to limit the records. But, the result is the query takes forever to load. How do I solve this?</p>
  
  <p>Here's my code:</p>
  
  <pre><code>with cte as(
  -- Anchor member definition
      SELECT  e.SponsorMemberID , e.MemberID, 1 AS Level
      FROM tblMember AS e 
      where e.memberid = @MemberID
  
  union all
  
  -- Recursive member definition
      select child.SponsorMemberID , child.MemberID, Level + 1
      from tblMember child 
  
  join cte parent
  
  on parent.MemberID = child.SponsorMemberID
  )
  -- Select the CTE result
      Select distinct a.* 
      from cte a
      option (maxrecursion 0)
  </code></pre>
  
  <p><strong>EDIT:</strong> remove unnecessary code to easy understand</p>
  
  <p><strong>SOLVED:</strong> So the issue is not came from <code>maxrecursion</code>. It's from the CTE. I don't know why but possibly it contain any sponsor cycles: A -> B -> C -> A -> ... (Thanks to @HABO)</p>
  
  <p>I tried this method and it works. <a href="https://stackoverflow.com/questions/13718168/infinite-loop-in-cte-when-parsing-self-referencing-table">Infinite loop in CTE when parsing self-referencing table</a></p>