[MS-SQL] 프로시저를 사용했을 때 in절 사용 방법(iterate 구현하기)

쿼리를 작성시 IN절을 사용하여 여러개의 값을 받아서 일치하는 값들에 대한 리스트 결과를 받아야할 때가 있습니다.

만약 ORM 중에 iBATIS(아이바티스) 혹은 마이바티스를 사용중이라면 쿼리를 구현하는 xml쪽 파일에서 iterate를 사용하여 처리할 수 있습니다. 

ORM을 사용하고 있지만 단순하게 프로시저를 호출해주는 연결자 역할만 하고 있다면???

모든 비즈니스 로직이 프로시저에 있는 경우가 있어요. 유지보수의 편의성 때문이겠지요. 서버사이드 코드를 작성하게 되면 소스 배포할 때 마다 기안 태워야하고, 아파치 서버 재시작해야하는 이런 저런 귀찮은 일들이 많이 생기지요. 그런 반면에 모든 비즈니스 로직이 프로시저에 있다면 Alter문 한번으로 모든게 해결됩니다.

서두가 길었네요.

여러개의 값을 받아 프로시저에서 조건절에 in절로 처리하는 방법에 대해 알아봅니다. 

이렇게 처리하는 경우, in절은 사용할 수 없어요.  파라미터 값에 콤마 혹은 다른 구분자로 값을 넘긴 후 문자열을 분리하여 테이블 값으로 리턴하는 Function 을 하나 만들어요. 그런다음에 IN절의 대상이 되는 테이블과 INNER JOIN를 사용하여 처리할 수 있어요.

문자열 분리 함수 생성(Create function)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION FN_RTN_TBL_SPLIT_STR
(    
   @PARAMS_STR			VARCHAR(MAX), 
   @STR_DELIMITER    VARCHAR(2) 
)
RETURNS @TBL_ARRAY TABLE (INDEX_NO INT IDENTITY, Value VARCHAR(MAX), PRIMARY KEY (INDEX_NO))
BEGIN
    DECLARE	
		@V_POST_START   INT,
        @V_POS_END		INT,
        @V_LEN_DELIM    TINYINT,
        @V_EXIT         TINYINT,
        @VC_STR          NVARCHAR(MAX)

    SET @V_POST_START = 1
    SET @V_POS_END = 1
    SET @V_LEN_DELIM = LEN(@STR_DELIMITER)

    SET @V_EXIT = 0
    IF @PARAMS_STR IS NOT NULL AND @PARAMS_STR <> '' 
    BEGIN
      WHILE @V_EXIT = 0
      BEGIN
         -- 구분자 기준으로 다음 문자의 위치 검색
         SET @V_POS_END = CHARINDEX(@STR_DELIMITER, @PARAMS_STR, @V_POST_START)

         IF @V_POS_END <= 0
         BEGIN
               SET @V_POS_END = LEN(@PARAMS_STR) + 1
               SET @V_EXIT = 1
         END

         -- 앞뒤 빈값 제거 LTRIM, RTRIM을 적용
         SET @VC_STR = LTRIM(RTRIM(SUBSTRING(@PARAMS_STR, @V_POST_START, @V_POS_END - @V_POST_START)))

         -- 테이블 INSERT
         IF @VC_STR <> 'NULL'
            INSERT INTO @TBL_ARRAY (Value) VALUES (@VC_STR)
         ELSE
            INSERT INTO @TBL_ARRAY (Value) VALUES (NULL)

         -- 다음 검색 위치로 이동
         SET @V_POST_START = @V_POS_END + @V_LEN_DELIM
      END
    END
    ELSE
    BEGIN
       INSERT INTO @TBL_ARRAY (Value) VALUES (NULL)
    END
    RETURN
END

[쿼리 실행 예시]

가령, 파라미터 값으로 “A,B,C,D,E,F,G”를 전달 받았을 때 위에 생성한 함수를 SELECT한 결과 입니다.

1. WHERE절에 사용하는 방법

조건절에 사용하는 방법입니다. 값이 있거나 없을 때 무조건 태워야함으로  @P_SEARCH_STR = ” OR 절이 추가됩니다.

ORM에서 처리했다면 IF문을 사용하여 분기하면 되지만, 프로시저에서 IF문 사용을 통한 분기는 미친짓입니다. 

DECLARE @P_SEARCH_STR	NVARCHAR(MAX) = ''
				
--WHERE절에 사용하는 경우
SELECT * FROM TEST_MASTER a
WHERE a.USE_YN= 'Y'
AND (@P_SEARCH_STR = '' OR EXISTS 
(
	SELECT 'X'
	FROM TEST_LIB v
	INNER JOIN FN_RTN_TBL_SPLIT_STR (@P_SEARCH_STR, ',') c ON (v1.TEST_ID = c.VALUE)
	WHERE v.ABC = a.ABC
))

2. INNER JOIN를 사용하는 경우

SELECT * FROM TEST_MASTER a
INNER JOIN FN_TBL_SPLIT_NSTR (@P_SEARCH_STR, ',') c1 ON a.TEST_ID = c1.value
WHERE a.USE_YN= 'Y'

아래와 같이 동적 쿼리를 생성하여 처리하는 방법도 있어요.

AND ('''+@P_SEARCH_STR		+ '''	= '''' 
	OR PROD_ID IN (
		SELECT Value FROM FN_RTN_TBL_SPLIT_STR(''' + @P_SEARCH_STR + ''', ''|'')
    	)
)

끝