[MSSQL] 저장 프로시저, 함수, 트리거 또는 뷰의 최대 중첩 수준(32) 처리 방법

CTE 를 쓰는 경우 아래와 같이 해결할 수 있겠지만

국내 검색 결과에는 모두 CTE의 경우에 대한 답변 뿐이라 영문 검색을 할 수 밖에 없었다.

"procedure, function, trigger, or view nesting level exceeded (limit 32)"

결론은 프로시저나 트리거 뷰에서는 재귀최대횟수를 어찌할 수 없다.

;WITH CTE ...
SELECT *
FROM TABLE
OPTION (MAXRECURSION  0) -- 이 옵션을 주면 무한루프로 돌수있고 원하는 레벨이 있으면 줘도 된다.

저장 프로시저, 함수, 트리거 또는 뷰의 최대 중첩 수준은 32레벨을 넘을 수 없게 지정되어 있다.
어쩔 수 없이 해당 중첩을 분기하거나 32 이상은 포기해야 한다.

아래와 같이 RECURSIVE를 호출하는 부분에서 LEVEL에 대한 값을 인자로 전달하고 제어하면,
오류를 막을 수 있다.

재귀로 수행하는 SP 명은 [PROC_FIND_SOURCE_LINK] 이고

@R_CNT 변수를 입력파라메터로 전달했다.

재귀 호출 전에 해당 값을 증가 시켜 다음 레벨 호출 시 전달하고 

SP 내부에서는 @R_CNT를 체크해서 32회가 넘지 않도록 처리한다.

참고로 아래는 BOTTOM_UP 으로 트리를 순회하는 과정중에 사용한 SP이다.

ALTER PROC [dbo].[PROC_FIND_SOURCE_LINK](
	@START_LINK INT
	,@MAP_ID INT
	,@R_CNT INT = 1
)
AS
BEGIN
	DECLARE @TMP_WORK_DATE datetime2(4)
	DECLARE @FIND_LINK INT
	DECLARE @FIND_LINK_ST INT
	DECLARE @FIND_LINK_ED INT
	DECLARE @LINK2 INT
	DECLARE @LINK2_ST INT
	DECLARE @LINK2_ED INT
	DECLARE @LINK3 INT
	DECLARE @LINK3_ST INT
	DECLARE @LINK3_ED INT
	--print('[PROC_FIND_SOURCE_LINK] ' + convert(varchar(100),@START_LINK)+':'+convert(varchar(100),@MAP_ID))	
	WHILE (@R_CNT < 31)
	BEGIN
		IF EXISTS ( SELECT * FROM TMP_HRTM_LINK_SHAPE WHERE TYPE = 1 AND (LINK_ID_2 = @START_LINK OR LINK_ID_3 = @START_LINK) )
		BEGIN
			SELECT @TMP_WORK_DATE = WORK_DATE, @FIND_LINK = LINK_ID_1, @LINK2 = LINK_ID_2, @LINK3=LINK_ID_3 FROM TMP_HRTM_LINK_SHAPE WHERE TYPE = 1 AND (LINK_ID_2 = @START_LINK OR LINK_ID_3 = @START_LINK)	
			
			INSERT INTO TMP_HUID(HUID, USE_YN, WORK_DATE)
			SELECT UID,USE_YN,INSERT_DATE 
			FROM RTM_LINK_UID
			WHERE ORI_MAP_ID = @MAP_ID AND ORI_ID = @FIND_LINK
			--print(':1 '+convert(varchar(100),@R_CNT))
			SET @R_CNT = @R_CNT+1
			EXEC PROC_FIND_SOURCE_LINK @FIND_LINK,@MAP_ID,@R_CNT

			/*정역 검증 데이터 추가*/
			;WITH SORCE
			AS ( SELECT WORK_DATE,LINK_MAP_ID,LINK_ID,ST_ND_ID,ED_ND_ID FROM COPY_HRTM_LINK_CURE WHERE WORK_DATE = @TMP_WORK_DATE AND LINK_ID = @FIND_LINK AND LINK_MAP_ID = @MAP_ID AND OP_CODE = 'R')
			,TAR1 
			AS (SELECT WORK_DATE,LINK_MAP_ID,LINK_ID,ST_ND_ID,ED_ND_ID FROM COPY_HRTM_LINK_CURE WHERE WORK_DATE = @TMP_WORK_DATE AND LINK_ID = @LINK2 AND LINK_MAP_ID = @MAP_ID AND OP_CODE = 'I')
			,TAR2 
			AS (SELECT WORK_DATE,LINK_MAP_ID,LINK_ID,ST_ND_ID,ED_ND_ID FROM COPY_HRTM_LINK_CURE WHERE WORK_DATE = @TMP_WORK_DATE AND LINK_ID = @LINK3 AND LINK_MAP_ID = @MAP_ID AND OP_CODE = 'I')
			INSERT INTO COPY_HRTM_LINK_REVERSE(WORK_DATE,LINK_MAP_ID,SRC_LINK_ID,SRC_ST_ND,SRC_ED_ND,TAR_LINK_ID,TAR_ST_ND,TAR_ED_ND,TYPE,REVERSE) 
			SELECT *
			FROM (SELECT DISTINCT A.WORK_DATE,A.LINK_MAP_ID,A.LINK_ID,A.ST_ND_ID,A.ED_ND_ID,B.LINK_ID AS T_LINK_ID,B.ST_ND_ID AS T_ST_ND_ID,B.ED_ND_ID AS T_ED_ND_ID,1 AS T ,CASE WHEN @LINK2 > @LINK3 THEN 1 ELSE 0 END AS C
			FROM SORCE A JOIN TAR1 B ON A.WORK_DATE = B.WORK_DATE AND A.LINK_MAP_ID = B.LINK_MAP_ID
			UNION
			SELECT DISTINCT A.WORK_DATE,A.LINK_MAP_ID,A.LINK_ID,A.ST_ND_ID,A.ED_ND_ID,B.LINK_ID AS T_LINK_ID,B.ST_ND_ID AS T_ST_ND_ID,B.ED_ND_ID AS T_ED_ND_ID,1 AS T,CASE WHEN @LINK2 > @LINK3 THEN 0 ELSE 1 END AS C
			FROM SORCE A JOIN TAR2 B ON A.WORK_DATE = B.WORK_DATE AND A.LINK_MAP_ID = B.LINK_MAP_ID	) A
			OPTION ( MAXRECURSION 0 )

			BREAK;
		END
		ELSE IF EXISTS ( SELECT * FROM TMP_HRTM_LINK_SHAPE WHERE TYPE = 2 AND LINK_ID_3 = @START_LINK )
		BEGIN
			--print(':2-1 '+convert(varchar(100),@R_CNT))
			SET @R_CNT = @R_CNT+1
			--한쪽 링크 따라가기
			SELECT @FIND_LINK = LINK_ID_1, @LINK2 = LINK_ID_2, @LINK3=LINK_ID_3  FROM TMP_HRTM_LINK_SHAPE WHERE TYPE = 2 AND LINK_ID_3 = @START_LINK
			
			INSERT INTO TMP_HUID(HUID, USE_YN, WORK_DATE)
			SELECT UID,USE_YN,INSERT_DATE 
			FROM RTM_LINK_UID
			WHERE ORI_MAP_ID = @MAP_ID AND ORI_ID = @FIND_LINK

			EXEC PROC_FIND_SOURCE_LINK @FIND_LINK,@MAP_ID,@R_CNT

			--반대쪽 링크 따라가기
			--print(':2-2 '+convert(varchar(100),@R_CNT))			
			SELECT @FIND_LINK = LINK_ID_2, @LINK2 = LINK_ID_1, @LINK3=LINK_ID_3  FROM TMP_HRTM_LINK_SHAPE WHERE TYPE = 2 AND LINK_ID_3 = @START_LINK
			
			INSERT INTO TMP_HUID(HUID, USE_YN, WORK_DATE)
			SELECT UID,USE_YN,INSERT_DATE 
			FROM RTM_LINK_UID
			WHERE ORI_MAP_ID = @MAP_ID AND ORI_ID = @FIND_LINK

			EXEC PROC_FIND_SOURCE_LINK @FIND_LINK,@MAP_ID,@R_CNT

			
			/*정역 검증 데이터 추가*/
			SELECT @TMP_WORK_DATE = WORK_DATE, @LINK3 = LINK_ID_2, @LINK2 = LINK_ID_1, @FIND_LINK=LINK_ID_3  FROM TMP_HRTM_LINK_SHAPE WHERE TYPE = 2 AND LINK_ID_3 = @START_LINK
			-- 병합 전 링크 정보 @FIND_LINK
			SELECT @FIND_LINK_ST = ST_ND_ID, @FIND_LINK_ED = ED_ND_ID FROM COPY_HRTM_LINK_CURE WHERE WORK_DATE = @TMP_WORK_DATE AND LINK_ID = @FIND_LINK AND LINK_MAP_ID = @MAP_ID AND OP_CODE = 'I'

			-- 분할 후 링크 정보
			IF (SELECT COUNT(*) FROM COPY_HRTM_LINK_CURE WHERE WORK_DATE = @TMP_WORK_DATE AND LINK_ID = @LINK2 AND LINK_MAP_ID = @MAP_ID) = 2
			BEGIN
				SELECT @LINK2_ST = ST_ND_ID, @LINK2_ED = ED_ND_ID FROM COPY_HRTM_LINK_CURE WHERE WORK_DATE = @TMP_WORK_DATE AND LINK_ID = @LINK2 AND LINK_MAP_ID = @MAP_ID AND OP_CODE IN('U')
				SELECT @LINK3_ST = ST_ND_ID, @LINK3_ED = ED_ND_ID FROM COPY_HRTM_LINK_CURE WHERE WORK_DATE = @TMP_WORK_DATE AND LINK_ID = @LINK3 AND LINK_MAP_ID = @MAP_ID AND OP_CODE IN('R')
			END
			ELSE
			BEGIN
				SELECT @LINK2_ST = ST_ND_ID, @LINK2_ED = ED_ND_ID FROM COPY_HRTM_LINK_CURE WHERE WORK_DATE = @TMP_WORK_DATE AND LINK_ID = @LINK2 AND LINK_MAP_ID = @MAP_ID AND OP_CODE IN('R')
				SELECT @LINK3_ST = ST_ND_ID, @LINK3_ED = ED_ND_ID FROM COPY_HRTM_LINK_CURE WHERE WORK_DATE = @TMP_WORK_DATE AND LINK_ID = @LINK3 AND LINK_MAP_ID = @MAP_ID AND OP_CODE IN('U')
			END
			
			IF @LINK2_ST = @LINK3_ST --SOURCE ST/ND 중 ST 가 같으면 ED 가 다른것 기준으로 REVERSE
			BEGIN
				INSERT INTO COPY_HRTM_LINK_REVERSE(WORK_DATE,LINK_MAP_ID,SRC_LINK_ID,SRC_ST_ND,SRC_ED_ND,TAR_LINK_ID,TAR_ST_ND,TAR_ED_ND,TYPE,REVERSE)
				SELECT @TMP_WORK_DATE,@MAP_ID,@LINK2,@LINK2_ST,@LINK2_ED,@FIND_LINK,@FIND_LINK_ST,@FIND_LINK_ED,2,CASE WHEN @LINK2_ED <> @FIND_LINK_ED THEN 1 ELSE 0 END

				INSERT INTO COPY_HRTM_LINK_REVERSE(WORK_DATE,LINK_MAP_ID,SRC_LINK_ID,SRC_ST_ND,SRC_ED_ND,TAR_LINK_ID,TAR_ST_ND,TAR_ED_ND,TYPE,REVERSE)
				SELECT @TMP_WORK_DATE,@MAP_ID,@LINK3,@LINK3_ST,@LINK3_ED,@FIND_LINK,@FIND_LINK_ST,@FIND_LINK_ED,2,CASE WHEN @LINK3_ED <> @FIND_LINK_ED THEN 1 ELSE 0 END
			END
			ELSE IF @LINK2_ED = @LINK3_ED --SOURCE ST/ND 중 ND 가 같으면 ST 가 다른것 기준으로 REVERSE
			BEGIN
				INSERT INTO COPY_HRTM_LINK_REVERSE(WORK_DATE,LINK_MAP_ID,SRC_LINK_ID,SRC_ST_ND,SRC_ED_ND,TAR_LINK_ID,TAR_ST_ND,TAR_ED_ND,TYPE,REVERSE)
				 SELECT @TMP_WORK_DATE,@MAP_ID,@LINK2,@LINK2_ST,@LINK2_ED,@FIND_LINK,@FIND_LINK_ST,@FIND_LINK_ED,2,CASE WHEN @LINK2_ST <> @FIND_LINK_ST THEN 1 ELSE 0 END
				
				INSERT INTO COPY_HRTM_LINK_REVERSE(WORK_DATE,LINK_MAP_ID,SRC_LINK_ID,SRC_ST_ND,SRC_ED_ND,TAR_LINK_ID,TAR_ST_ND,TAR_ED_ND,TYPE,REVERSE)
				SELECT @TMP_WORK_DATE,@MAP_ID,@LINK3,@LINK3_ST,@LINK3_ED,@FIND_LINK,@FIND_LINK_ST,@FIND_LINK_ED,2,CASE WHEN @LINK3_ST <> @FIND_LINK_ST THEN 1 ELSE 0 END
			END
			ELSE
			BEGIN
				INSERT INTO COPY_HRTM_LINK_REVERSE(WORK_DATE,LINK_MAP_ID,SRC_LINK_ID,SRC_ST_ND,SRC_ED_ND,TAR_LINK_ID,TAR_ST_ND,TAR_ED_ND,TYPE,REVERSE)
				SELECT @TMP_WORK_DATE,@MAP_ID,@LINK2,@LINK2_ST,@LINK2_ED,@FIND_LINK,@FIND_LINK_ST,@FIND_LINK_ED,2,0

				INSERT INTO COPY_HRTM_LINK_REVERSE(WORK_DATE,LINK_MAP_ID,SRC_LINK_ID,SRC_ST_ND,SRC_ED_ND,TAR_LINK_ID,TAR_ST_ND,TAR_ED_ND,TYPE,REVERSE)
				SELECT @TMP_WORK_DATE,@MAP_ID,@LINK3,@LINK3_ST,@LINK3_ED,@FIND_LINK,@FIND_LINK_ST,@FIND_LINK_ED,2,0
			END			

			BREAK;
		END		
		ELSE
			BREAK;
		
		--PRINT (CONVERT(VARCHAR(100),@R_CNT))
	END		
END

관련 자료 : https://www.sqlservercurry.com/2015/03/sql-server-stored-procedure-recursion.html