How to Solve SQL Server's Max Recursion Error in Recursive CTEs?-Mysql Tutorial-php.cn

How to Solve SQL Server's Max Recursion Error in Recursive CTEs?

Patricia Arquette

Release： 2025-01-10 06:02:40

Original

127 people have browsed it

How to Solve SQL Server's Max Recursion Error in Recursive CTEs?

Conquering the SQL Server Recursion Limit in Recursive CTEs

When working with recursive Common Table Expressions (CTEs) in SQL Server, you might encounter the dreaded "maximum recursion depth exceeded" error. This happens when your nested recursive queries surpass the database's predefined limit.

The solution involves using the maxrecursion option:

Here's how to modify your query to address this issue:

<code class="language-sql">WITH EmployeeHierarchy AS (
    SELECT 
        EMP_SRC_ID_NR AS Id, USR_ACV_DIR_ID_TE AS Uuid, 
        ISNULL(Employees.APV_MGR_EMP_ID, '0') 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 AS Id, USR_ACV_DIR_ID_TE AS Uuid, 
        ISNULL(Employees.UPS_ACP_EMP_NR, '1') 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, 
        ISNULL(Employees.APV_MGR_EMP_ID, '2')  
    FROM 
        dbo.[tEmployees] AS Employees WITH (NOLOCK)
    JOIN 
        EmployeeHierarchy ON Employees.APV_MGR_EMP_ID = EmployeeHierarchy.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 EmployeeHierarchy
OPTION (MAXRECURSION 0);</code>

Copy after login

By appending OPTION (MAXRECURSION 0), you effectively eliminate the recursion limit, allowing the query to run indefinitely. However, be cautious when using MAXRECURSION 0. Ensure your CTE logic is correct to prevent infinite loops which could lead to performance issues or crashes. Consider alternative approaches if the recursion depth is unexpectedly large. Using ISNULL instead of CASE simplifies the code and improves readability.

The above is the detailed content of How to Solve SQL Server's Max Recursion Error in Recursive CTEs?. For more information, please follow other related articles on the PHP Chinese website!