sqlsql-serverrecursioncommon-table-expression

SQL Recursion CTE infinite loop


I have a train lines table which looks like this:

enter image description here

The task is to get every possible destination from a given Start (recursively).

I've tried to do it like this:

    with cte (st, nd) as(
    select from1, to1
    from RailwayTbl
    where from1 = 'Berlin'

    union all

    select e.from1, e.to1
    from RailwayTbl e join cte
    on cte.nd = e.from1
    )
    select * from cte;

When executed, I got the following error:

The statement terminated. The maximum recursion 100 has been exhausted before statement completion. ((It is an infinite loop..))

And when I removed the all and used union only I got this error:

Recursive common table expression 'cte' does not contain a top-level UNION ALL operator.

Any ideas?
Thanks in advance!


Solution

  • You need to stop the recursion, which is a pain in SQL Server. The following keeps a string of the visited stations to stop the recursion:

     with cte as (
           select from1 as st, to1 as nd, concat(',', st, ',', to1) as dests, 1 as lev
           from RailwayTbl
           where from1 = 'Berlin'
           union all
           select e.from1, e.to1, concat(cte.dests, e.to1, ',') as dests, lev + 1
           from RailwayTbl e join
                cte
                on cte.nd = e.from1
           where dests not like concat('%,', e.nd, ',%')
        )
    select * from cte;
    

    I also added lev. Often I use where lev < 10 on the recursive part of the cte definition for debugging.