The maximum recursion 100 has been exhausted before statement completion

Question

I keep getting a max recursion error with this query. At first, I thought it was because null was being returned and then it would try and match the null values causing the error, however, I rewrote my query so nulls aren't returned and the error still occurs. What would be the best way to rewrite this function, so that the error will not occur

WITH EmployeeTree AS

(

    SELECT 

        EMP_SRC_ID_NR Id, USR_ACV_DIR_ID_TE Uuid, 

        CASE Employees.APV_MGR_EMP_ID 

           WHEN Null THEN '0' 

           ELSE Employees.APV_MGR_EMP_ID 

        END as  ApprovalManagerId 

    FROM 

        dbo.[tEmployees] as Employees WITH (NOLOCK)

    WHERE 

        APV_MGR_EMP_ID = @Id 

        and Employees.APV_MGR_EMP_ID is not null 

        and Employees.EMP_SRC_ID_NR is not null  

    UNION ALL

    SELECT 

        EMP_SRC_ID_NR Id, USR_ACV_DIR_ID_TE Uuid, 

        CASE Employees.UPS_ACP_EMP_NR 

           WHEN Null THEN '1' 

           ELSE Employees.UPS_ACP_EMP_NR 

        END as ApprovalManagerId 

    FROM 

        dbo.[tEmployees] as Employees WITH (NOLOCK)

    WHERE 

        UPS_ACP_EMP_NR = @Id 

        and Employees.APV_MGR_EMP_ID is not null 

        and Employees.EMP_SRC_ID_NR is not null  

    UNION ALL

    SELECT 

        Employees.EMP_SRC_ID_NR, Employees.USR_ACV_DIR_ID_TE, 

        CASE Employees.APV_MGR_EMP_ID 

            WHEN Null THEN '2' 

            ELSE Employees.APV_MGR_EMP_ID 

Answer 1

You can specify the maxrecursion option at the end of the query like this:...

from EmployeeTree

option (maxrecursion 0)

This will allows you to specify how often the CTE can recurse before generating an error.

Note: Maxrecursion 0 can allow infinite recursion.