title: TSQL CTE: How to avoid circular traversal?
body: <p>I have written a very simple CTE expression that retrieves a list of all groups of which a user is a member. </p>
  
  <p>The rules goes like this, a user can be in multiple groups, and groups can be nested so that a group can be a member of another group, and furthermore, groups can be mutual member of another, so Group A is a member of Group B and Group B is also a member of Group A. </p>
  
  <p>My CTE goes like this and obviously it yields infinite recursion:</p>
  
  <pre><code>            ;WITH GetMembershipInfo(entityId) AS( -- entity can be a user or group
                  SELECT k.ID as entityId FROM entities k WHERE k.id = @userId
                  UNION ALL
                  SELECT k.id FROM entities k 
                  JOIN Xrelationships kc on kc.entityId = k.entityId
                  JOIN GetMembershipInfo m on m.entityId = kc.ChildID
              )
  </code></pre>
  
  <p>I can't find an easy solution to back-track those groups that I have already recorded. </p>
  
  <p>I was thinking of using an additional varchar parameter in the CTE to record a list of all groups that I have visited, but using varchar is just too crude, isn't it?</p>
  
  <p>Is there a better way?</p>