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 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

User · Accepted Answer

Specify the maxrecursion option at the end of the query:

...
from EmployeeTree
option (maxrecursion 0)

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

User · Answer

it is just a sample to avoid max recursion error  we have to use option  maxrecursion 365   or option  maxrecursion 0    DECLARE  STARTDATE datetime   DECLARE  EntDt datetime   set  STARTDATE    01 01 2009     set  EntDt    12 31 2009    declare  dcnt int    with DateList as              select  STARTDATE DateValue        union all        select DateValue   1 from    DateList           where   DateValue   1  lt  convert VARCHAR 15   EntDt 101             select count    as DayCnt from        select DateValue DATENAME WEEKDAY  DateValue   as WEEKDAY from DateList   where DATENAME WEEKDAY  DateValue   not IN    Saturday   Sunday            a option  maxrecursion 365

[sql] The maximum recursion 100 has been exhausted before statement completion

Examples related to sql

Examples related to stored-procedures

Examples related to common-table-expression