最大递归100在语句完成前已用完 [英] The maximum recursion 100 has been exhausted before statement completion

问题描述

我一直收到这个查询的最大递归错误.

I keep getting a max recursion error with this query.

起初我认为这是因为返回了一个空值，然后它会尝试匹配导致错误的空值，但是，我重写了我的查询，因此没有返回空值并且错误仍然发生.

At first I thought it was because a 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 
        END  
    FROM 
        dbo.[tEmployees] as Employees WITH (NOLOCK)
    JOIN 
        EmployeeTree ON Employees.APV_MGR_EMP_ID = EmployeeTree.Id 
    where  
        Employees.APV_MGR_EMP_ID is not null 
        and Employees.EMP_SRC_ID_NR is not null             
)
SELECT 
    Id AS [EmployeeId], 
    Uuid AS [EmployeeUuid], 
    ApprovalManagerId AS [ManagerId] 
FROM EmployeeTree        

推荐答案

指定 查询末尾的 maxrecursion 选项:

...
from EmployeeTree
option (maxrecursion 0)

这允许您指定 CTE 在生成错误之前可以递归的频率.Maxrecursion 0 允许无限递归.

That allows you to specify how often the CTE can recurse before generating an error. Maxrecursion 0 allows infinite recursion.

这篇关于最大递归100在语句完成前已用完的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！