Untitled

WITH RecursiveCTE AS (
    -- Anchor member
    SELECT 
        ...,       -- columns
        1 AS Depth -- initial depth
    FROM 
        ...
    WHERE 
        ...

    UNION ALL

    -- Recursive member
    SELECT 
        ...,           -- columns
        Depth + 1      -- increment depth
    FROM 
        ...
    JOIN 
        RecursiveCTE 
    ON 
        ...
    WHERE 
        ...
        AND Depth < @MaxDepth  -- termination clause
)

-- Query to use the CTE
SELECT 
    ...
FROM 
    RecursiveCTE
WHERE 
    ...