title: The maximum recursion 100 has been exhausted before statement completion error showing in SQL Query
body: <p>"The maximum recursion 100 has been exhausted before statement completion" error showing in SQL Query</p>
  
  <pre><code>WITH DepartmentCTE AS
  (   SELECT  ID, 
          DepartmentName, 
          RootID, 
          RecursionLevel = 1, 
          ParentRoot = CAST('None' AS NVARCHAR(max)),
          LastParentCatID = RootID,
          DisplayOrder
  FROM    Department
  UNION ALL
  SELECT  cte.ID, 
          cte.DepartmentName,
          cte.RootID,
          cte.RecursionLevel + 1,
          ParentRoot = CASE WHEN cte.RecursionLevel = 1 THEN '' ELSE cte.ParentRoot + '&gt;' END + c.DepartmentName,
          LastParentCatID = c.RootID,
          cte.DisplayOrder
  FROM    DepartmentCTE cte
          INNER JOIN Department c
              ON c.ID = cte.RootID
  
  ), MaxRecursion AS
  (   SELECT  ID, 
          DepartmentName, 
          RootID, 
          ParentRoot, 
          RowNum = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY RecursionLevel DESC),
          DisplayOrder
  FROM    DepartmentCTE
  )
  SELECT  ID, DepartmentName, RootID, ParentRoot
  FROM    MaxRecursion 
  WHERE   RowNum = 1;
  </code></pre>