Stopping recursive CTE with a guard

Question

I have the following hierarchical data going from source to destination and i would like to stop when a reference to an existing source appears

create table #temp (source int, destination int);
insert into #temp values (1,3), (3,7), (7,9), (9,1);

WITH cte (Source, Destination, Level, Sources)
AS 
(
    SELECT Source, Destination, 0 AS Level, CAST(Source AS VARCHAR(MAX)) + ',' AS Sources
    FROM #temp 
    WHERE [Source] = 1

    UNION ALL

    SELECT t.[Source], t.Destination, cte.[Level] + 1, cte.Sources + CAST(t.Source AS VARCHAR(MAX)) + ','
    FROM #temp t        
        INNER JOIN cte ON cte.Destination = t.[Source] AND (CAST(t.Destination AS VARCHAR(MAX)) + ',' NOT LIKE '%' + cte.Sources + '%')
)
select * from cte

drop table #temp;

however i still get the max recursion error when running this. how should i write the guard clause properly? What I do want is the first 3 results.

Answer 1

Here, in the recursive member of the CTE:

INNER JOIN cte 
    ON  cte.Destination = t.[Source] 
    AND (CAST(t.Destination AS VARCHAR(MAX)) + ',' NOT LIKE '%' + cte.Sources + '%')

You have the LIKE operands the wrong way around. Instead:

INNER JOIN cte 
    ON cte.Destination = t.[Source] 
    AND cte.Sources NOT LIKE CONCAT('%', t.Destination, '%')

That is: the destination should be belong to the list of sources that were already visited. Note that using CONCAT() forces the conversion of the number to a string, which shortens the expression.

Demo on DB Fiddle