Recursive CTE never finishes

Question

This recursive CTE runs forever (never returns results), when obtaining the same results by hand would take about 10 seconds, with most of that being copy-pasting.

Did I misimplement the RekeyLevel part? Is it not leveling up appropriately?
How would I make it so the recursion stops when no results are found, rather than needing a failsafe like RekeyLevel <= 2?

Current query:

with RekeysAllLevelsDeep as (

select
 a.claimid as Rekey
,a.ClaimIDAdjFromOrig as Original
,0 as RekeyLevel

from <base table> (nolock) a

where a.ClaimIDAdjFromOrig is not null
    and a.ClaimIDAdjFromOrig <> a.ClaimID


union all


select
 a.claimid as Rekey
,a.ClaimIDAdjFromOrig as Original
,RekeyLevel + 1

from RekeysAllLevelsDeep

join <base table> (nolock) a
    on RekeysAllLevelsDeep.Original = a.ClaimID

where a.ClaimIDAdjFromOrig is not null
    and a.ClaimIDAdjFromOrig <> a.ClaimID
    and RekeyLevel <= 2
)

select distinct
 Rekey
,Original
,RekeyLevel

from RekeysAllLevelsDeep

where Original is not null
    and Original <> Rekey
    and Rekey = '(<number>)'

Limiting the depth manually, e.g. with `RekeyLevel`, and examining the results usually points to the problem. [This](https://stackoverflow.com/questions/15080922/infinite-loop-cte-with-option-maxrecursion-0/15081353#15081353) answer demonstrates one way of handling loops in data during recursion. Tip: It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. Note that `tsql` narrows the choices, but does not specify the database. — HABO, Aug 01 '17 at 23:55
@HABO Great tips, thank you! I added a `SQL-Server-2014` tag. Also, I did attempt to limit the depth manually (`RekeyLevel <= 2`), but still have the issue. That's what made me guess that I am not leveling appropriately and led to the first part of my question. Thanks for giving it a look, hopefully someone spots something. — puzzlepiece87, Aug 02 '17 at 14:15

score 0 · Accepted Answer · answered Aug 02 '17 at 17:14

I needed to move the condition I used outside the recursive CTE, and Rekey = '(<number>)', inside of it. Doing so made the recursive CTE return correct results immediately. Having the condition outside the recursive CTE meant that the recursive CTE was doing this recursion for every number in the entire table.

Recursive CTE never finishes

1 Answers1