How to detect cyclical reference in SQL Server Query - SQL Server 2017

Question

I have a recursive WITH query in SQL Server 2017:

;WITH rec AS (
    SELECT
       col1 AS root_order
      ,col1
      ,col2
      ,col3
      ,col4
      ,col5
      ,col6
      ,col7
      ,col8
      ,col9

    FROM
        TableA


    UNION ALL

    SELECT
        rec.root_order,
        TableA.col2,
        TableA.col3,
        TableA.col4,
        TableA.col5,
        TableA.col6,
        TableA.col7,
        TableA.col8,
        TableA.col9,
        rec.the_level

    FROM
        rec
            INNER JOIN TableA on rec.Details = TableA.Orders
    )

    SELECT DISTINCT * FROM rec

This yields a: The statement terminated. The maximum recursion 100 has been exhausted before statement completion. error.

I have tried: OPTION (maxrecursion 0) to let it continue. But when I do that, the query infinitely loops, so that doesn't work.

In Oracle, I can use CONNECT BY ROOT and CONNECT BY PRIOR and NOCYCLE, but I know things like that aren't available in SQL Server. So I found this MSDN link which suggest something of the form:

with hierarchy
as
(
select
    child, 
    parent, 
    0 as cycle,
    CAST('.' as varchar(max)) + LTRIM(child) + '.' as [path]
from
    #hier
where
    parent is null

union all

select
    c.child,
    c.parent,
    case when p.[path] like  '%.' + LTRIM(c.child) + '.%' then 1 else 0 end as cycle,
    p.[path] + LTRIM(c.child) + '.' as [path]
from
    hierarchy as p
    inner join
    #hier as c
    on p.child = c.parent
    and p.cycle = 0
)
select
    child,
    parent,
    [path]
from
    hierarchy
where
    cycle = 1;
go

For finding the cycles (or avoiding them). I cannot seem to take my current query and edit it in that fashion. How can I edit my current SQL to perform the cyclic reference detection like in the MSDN article?

Some sample data as requested here in SQL FIDDLE.

Answer 1

What I normally do is pretty simple. In the anchor query (the first part of the CTE), I include a value "1 AS Level" in the select list. Then in the bottom query, I select Level + 1 as the Level, so I know what depth I'm up to. Then I can just put a sanity clause into the bottom query to limit the depth i.e. WHERE LEVEL <= 10 or whatever depth you want. But yes, you still need MAXRECURSION set to 0 if you want to go above 100 levels.

Here's an example based on AdventureWorks:

WITH Materials (BillOfMaterialsID, ProductName, ProductAssemblyID, ComponentID, [Level])
AS
(
    SELECT bom.BillOfMaterialsID, 
           p.[Name], 
           bom.ProductAssemblyID, 
           bom.ComponentID, 
           1
    FROM Production.BillOfMaterials AS bom
    INNER JOIN Production.Product AS p
    ON bom.ComponentID = p.ProductID 
    AND bom.EndDate IS NULL
    WHERE bom.ProductAssemblyID IS NULL

    UNION ALL

    SELECT bom.BillOfMaterialsID, 
           p.[Name], 
           bom.ProductAssemblyID, 
           bom.ComponentID, 
           m.[Level] + 1
    FROM Production.BillOfMaterials AS bom
    INNER JOIN Production.Product AS p
    ON bom.ComponentID = p.ProductID 
    INNER JOIN Materials AS m
    ON bom.ProductAssemblyID = BOM.ComponentID 
    WHERE m.[Level] <= 5
)
SELECT m.BillOfMaterialsID, 
       m.ProductName, 
       m.ProductAssemblyID, 
       m.ComponentID, 
       m.[Level]
FROM Materials AS m
ORDER BY m.[Level], m.BillOfMaterialsID;