Maksymalna rekursja 100 została wyczerpana przed zakończeniem instrukcji

136

Ciągle otrzymuję max recursion error z tym zapytaniem.

Na początku myślałem, że dzieje się tak, ponieważ zwracana jest wartość null, a następnie spróbuje dopasować wartości null powodujące błąd, jednak przepisałem zapytanie, aby wartości null nie były zwracane, a błąd nadal występuje.

Jaki byłby najlepszy sposób na przepisanie tej funkcji, aby błąd się nie pojawił

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

sql stored-procedures common-table-expression

— POMÓŻ MI
źródło

Ta linia może zostać zastąpiona przez COALESCE():

CASE Employees.APV_MGR_EMP_ID             WHEN Null THEN '0'             ELSE Employees.APV_MGR_EMP_ID          END as  ApprovalManagerId

=COALESCE(Employees.APV_MGR_EMP_ID, 0) AS ApprovalManagerID

— David Faber

Odpowiedzi:

249

Określ opcję maxrecursion na końcu zapytania:

...
from EmployeeTree
option (maxrecursion 0)

Pozwala to określić, jak często CTE może się powtarzać przed wygenerowaniem błędu. Maxrecursion 0 pozwala na nieskończoną rekurencję.

— Andomar
źródło

hmm to zadziałało, ale zapytanie zwróciło o wiele więcej wierszy niż powinno

— HELP_ME

@bugz Maxrecursion 0 ma teraz wpływ na twoje zapytanie, czy musisz szukać problemu gdzie indziej

— t-clausen.dk

ahh to był cykliczny odnośnik w moich danych, dzięki za pomoc

— HELP_ME

+1 Użyłem tej opcji do debugowania podobnego problemu. Jeśli zapytanie jest nieskończenie rekurencyjne, musisz anulować zapytanie w Management Studio po jego wykonaniu, w przeciwnym razie serwer będzie buforował wiersze do momentu, gdy klientowi zabraknie pamięci.

— Iain Samuel McLean Starszy

Chociaż może to rozwiązać problem w sytuacjach, w których zapytanie ma się powtarzać bardzo głęboko, może to być po prostu maskowanie problemu w zapytaniu.

— Christian Findlay

jest to tylko próbka, aby uniknąć maksymalnego błędu rekurencji. musimy skorzystać z opcji (maxrecursion 365); lub opcja (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 < 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);

— Mou
źródło