SQL Server Stored Procedure, CTE Recursion Limit (재귀횟수제한)

Recursion(재귀) 호출 시 반복 횟수가 제한이 있다.

아래 예제를 통하여 확인해 보자.

#.1 Stroed Procedure

use tempdb
go

CREATE PROCEDURE recurseProc (@cnt int)
AS
BEGIN
PRINT @cnt;
SET @cnt += 1;
EXEC recurseProc @cnt;
END;
GO

-- Procedure 생성 후 아래 실행해보자.
EXEC recurseProc 1;

/*

1
2
3 
.
.
31
32

메시지 217, 수준 16, 상태 1, 프로시저 recurseProc, 줄 70
저장 프로시저, 함수, 트리거 또는 뷰의 최대 중첩 수준(32)을 초과했습니다.
*/

#.2 CTE (Common Table expression) : 공통 테이블 식
CTE로 재귀쿼리를 작성할 때는 조심해야 할 것 같다.

use [AdventureWorks2008R2]
go

WITH Emp_CTE AS (

SELECT businessentityid, JobTitle, BirthDate

FROM HumanResources.Employee

WHERE businessentityid is Not NULL

UNION ALL

SELECT e.businessentityid, e.JobTitle, e.BirthDate

FROM HumanResources.Employee e

INNER JOIN Emp_CTE ecte ON ecte.businessentityid = e.businessentityid

)
SELECT *
FOM Emp_CTE 
 

/*

메시지 530, 수준 16, 상태 1, 줄 84
문이 종료되었습니다. 문이 완료되기 전에 최대 재귀 횟수(100)가 초과되었습니다.

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 3 has been exhausted before statement completion.

*/

-- 아래 옵션으로 기본제한을 풀 수 있다.
OPTION- (MAXRECURSION 150)
GO

   

[참고문서]

잘못 구성된 재귀적 CTE로 인해 무한 루프가 발생할 수 있습니다. 예를 들어 재귀 멤버 쿼리 정의가 부모 열과 자식 열 모두에 대해 동일한 값을 반환하면 무한 루프가 생성된 것입니다. 무한 루프를 막기 위해서는 INSERT, UPDATE, DELETE 또는 SELECT 문의 OPTION 절에서 MAXRECURSION 힌트와 0부터 32,767 사이의 값을 사용하여 특정 문에 허용되는 재귀 수준을 제한할 수 있습니다. 이 방법으로 루프를 발생시키는 코드 문제를 해결할 때까지 문의 실행을 제어할 수 있습니다. 서버 차원의 기본값은 100입니다. 0을 지정하면 제한이 적용되지 않습니다. 하나의 문에는 하나의 MAXRECURSION 값만 지정할 수 있습니다.

https://msdn.microsoft.com/ko-kr/library/ms175972.aspx