T-SQL Recursive Select Circular dependency

Question

I've got self dependant entities (a) in my database, which are referenced from another entity (b), and given a specific (b) entity, I need to get all the (a) entities that are needed. These are many to many mappings, so I have a separate mapping table. I think a recursive Select with a CTE is my best bet, but I'm running into an issue:

This Fiddle illustrates my issue. If some user introduces a circular reference, my recursive select grinds to a screeching halt. I've been wracking my brain to try to find some way to fix this. It should be noted that though I have introduced Foreign Keys in the fiddle, foreign keys aren't actually honored by the system I'm using (long standing argument with the DBAs) - I introduced them to make data flow more clear.

The recursive query, for those who don't want to click through to the fiddle:

WITH recur(objID) AS (
    SELECT usesObjID
        FROM #otherObj
        WHERE otherObjID = 1
    UNION ALL
    SELECT slaveObjID
        FROM #objMap
            INNER JOIN recur
                on #objMap.masterObjID = recur.objID
)SELECT objID from recur

Any Ideas out there? This design isn't in production, so I can change schema somewhat, but I'd like not to rely on discovering circular references upon insertion, unless it can be done by T-SQL.

Answer 1

It's possible to set the MAXRECURSION of the CTE, which will prevent the infinite loop, but you'll still get weird results since the query will continue to run in the loop until the max recursion is hit.

The challenge is that the loop involves multiple steps, so you can't just check the immediate parent of the child in order to determine if you're in a loop.

One way to handle this would be to add an additional column to the CTE... this new column, tree, tracks all the IDs that have been included so far, and stops when an ID repeats.

WITH recur(objID, Tree) AS (
    SELECT 
        usesObjID, 
        CAST(',' + CAST(usesObjID AS VARCHAR) + ',' AS VARCHAR) AS Tree
    FROM otherObj
    WHERE otherObjID = 1
    UNION ALL
    SELECT 
        slaveObjID, 
        CAST(recur.Tree + CAST(slaveObjID AS VARCHAR) + ',' AS VARCHAR) AS Tree
    FROM objMap
        INNER JOIN recur
            ON objMap.masterObjID = recur.objID
    WHERE recur.Tree NOT LIKE '%,' + CAST(slaveObjID AS VARCHAR) + ',%'  
)SELECT objID from recur

Sql Fiddle Link