title: CTE goes recursive and fails
body: <p>I have created a very small CTE recursive function but seems it goes recursive all the time and fails.
  Please find details inline:</p>
  
  <p>Table Name &amp; Data:</p>
  
  <pre><code>insert into dbo.HierarchyEmployee     
  Values
  (1,1, 'Name1'),
  (2,1, 'Name2'),
  (3,4, 'Name3'),
  (4,5, 'Name4'),
  (5,2, 'Name5'),
  (6,4, 'Name6'),
  (7,1, 'Name7'),
  (8,8, 'Name8'),
  (9,3, 'Name9'),
  (10,1, 'Name10')
  </code></pre>
  
  <p>Ideal Result Set:</p>
  
  <pre><code>HierarchyParent HierarchyID Name
  1   1   Name
  1   2   Name2
  1   7   Name7
  1   10  Name10
  </code></pre>
  
  <p>I tried to achieve this through reclusive CTE as I wanted to look how recursion works.</p>
  
  <p>Below is the query used:</p>
  
  <pre><code>WITH CTE_HierarchyEmployee
  AS
  (
      SELECT  H.HierarchyID,
              H.HierarchyParent,
              H.Name
              FROM dbo.HierarchyEmployee H
              WHERE H.HierarchyID = 1
              UNION ALL
      SELECT  H.HierarchyID,
              H.HierarchyParent,
              H.Name
              FROM
              dbo.HierarchyEmployee H
              INNER JOIN  CTE_HierarchyEmployee CTEH
              ON H.HierarchyParent = cteh.HierarchyID
  )
  SELECT * FROM CTE_HierarchyEmployee
  </code></pre>
  
  <p>Error:</p>
  
  <blockquote>
    <p>Msg 530, Level 16, State 1, Line 41</p>
    
    <p>The statement terminated. The maximum recursion 100 has been exhausted before statement completion.</p>
  </blockquote>
  
  <p>Appreciate you input on how to resolve the revulsion.</p>