Convert Oracle CONNECT BY query to SQL Server query

Question

How to convert this Oracle code structure to SQL Server:

SELECT LEVEL, EMPNO, ENAME, JOB, MGR FROM EMP
CONNECT BY MGR = PRIOR EMPNO
START WITH MGR IS NULL
ORDER BY LEVEL;

Answer 1

SQL Server does not have CONNECT BY. You need to use a recursive CTE.

Place the START WITH in the WHERE filter of the anchor part (the first part of the CTE).

In the recursive part (the second half), rejoin the CTE to EMP with the CONNECT BY condition.

WITH cte AS (
    SELECT
        LEVEL = 1,
        e.EMPNO,
        e.ENAME,
        e.JOB,
        e.MGR
    FROM EMP e
    WHERE e.MGR IS NULL

    UNION ALL

    SELECT
        cte.LEVEL + 1,
        e.EMPNO,
        e.ENAME,
        e.JOB,
        e.MGR
    FROM EMP e
    JOIN cte ON e.MGR = cte.EMPNO
)

SELECT
    cte.LEVEL,
    cte.EMPNO,
    cte.ENAME,
    cte.JOB,
    cte.MGR
FROM cte
ORDER BY cte.LEVEL;