SQL Server recursive query in a CTE issue

Question

I have a table called Nodes consisting of base doc, current doc, and target doc:

BaseDocType . BaseDocID
DocType . DocID
TargetDocType . TargetDocID ..

I want to fetch all the related nodes for any specific node. This is what I have so far:

WITH CTE1 (ID, BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID) AS
(
    SELECT
        ID, BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID
    FROM
        Doc.Nodes 
    WHERE
        DocType = 8 AND DocID = 2

    UNION ALL

    SELECT
        a.ID, a.BaseDocType, a.BaseDocID, a.DocType, a.DocID, a.TargetDocType, a.TargetDocID
    FROM
        Doc.Nodes a
    INNER JOIN
        CTE1 b ON (a.BaseDocType = a.BaseDocType 
                   AND a.BaseDocID = b.BaseDocID 
                   AND a.DocType != b.DocType 
                   AND a.DocID != b.DocID)
)
SELECT *
FROM CTE1

But the query is not working. I get this error:

Msg 530, Level 16, State 1, Line 8
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

![Example][1]

How can I fix this?

[here is an example that am trying to achieve][2]

Answer 1

The problem is that the anchor row (ID = 2) finds the related row with ID = 1, but the relationship also works in the other direction. So row 1 then finds row 2, and on and on ad infinitum. You need a termination condition of some sort on the recursion.

I'm not sure why the model given has base / document / target relationships and no target data.

Some set up with table-valued variables:

DECLARE @Nodes TABLE (ID INT, BaseDocType INT, BaseDocID INT, DocType INT, DocID INT, TargetDocType INT NULL, TargetDocID INT NULL);

INSERT INTO @Nodes 
VALUES 
    (1, 10, 6, 100, 2034, NULL, NULL),
    (2, 10, 6, 8, 2, NULL, NULL);

Rather than specifying the start document details in the anchor, you can pass those through:

With CTE1 (ID, StartDocType, StartDocID, BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID)
As
(
    Select ID, DocType, DocID, BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID
    From @Nodes 

    Union All

    Select a.ID, b.StartDocType, b.StartDocID, a.BaseDocType, a.BaseDocID, a.DocType, a.DocID, a.TargetDocType, a.TargetDocID
    From @Nodes a
        INNER JOIN CTE1 b 
            ON (
                a.BaseDocType = b.BaseDocType 
                AND a.BaseDocID = b.BaseDocID 
                AND a.DocType != b.DocType 
                AND a.DocID != b.DocID
                AND NOT (a.DocType = b.StartDocType AND a.DocID = b.StartDocID)
                )
)
Select *
From CTE1
Where StartDocType=8 and StartDocID = 2

This helps, as the recursive part can avoid reprocessing the start row and looping back from the start. However, with more data you may well hit the same issue again.

The bluntest way to terminate the recursion is to limit the recursion depth yourself by tracking the depth and limiting it in the recursive part:

With CTE1 (rlevel, ID, StartDocType, StartDocID, BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID)
As
(
    Select 1, ID, DocType, DocID, BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID
    From @Nodes

    Union All

    Select b.rlevel + 1, a.ID, b.StartDocType, b.StartDocID, a.BaseDocType, a.BaseDocID, a.DocType, a.DocID, a.TargetDocType, a.TargetDocID
    From @Nodes a
        INNER JOIN CTE1 b 
            ON (
                a.BaseDocType = b.BaseDocType 
                AND a.BaseDocID = b.BaseDocID 
                AND a.DocType != b.DocType 
                AND a.DocID != b.DocID
                )
    WHERE b.rlevel < 10
)
Select DISTINCT ID, BaseDocType, BaseDocID, DocType, DocID, TargetDocType, TargetDocID
From CTE1
Where StartDocType=8 and StartDocID = 2

There may be better ways to restrict the recursion, but that will need more knowledge of the underlying data.