[MS-SQL] 프로시저(procedure) 생성시 검색 조건이 많을 경우에 대한 처리 방법(동적쿼리 심화)

자바 스프링 프레임워크 기반에 ORM은 아이바티스를 사용하는 개발 환경입니다. 유지보수의 편의성을 위해 아이바티스에서는 비즈니스 로직이 없고, 파라미터값들만 프로시저로 넘겨주는 중간자 역할만 하고 있습니다. 검색조건이 적으면 문제가 없지만 검색조건이 많아지면 프로시저 내에서 검색조건에 따른 전체 쿼리 분기는 해결책이 될 수 없어요. 검색 조건이 많아지면 무조건 동적쿼리 생성 후 EXCUTE 문을 실행하는 것을 추천 합니다.

procedure 생성시 검색 조건이 많을 때 동적쿼리 생성 시나리오

다음은 이해를 돕기위한 우편번호 예제입니다.

1. 도 단위 검색 조건 (“전북”, “전남”, “서울”……)

2. 도 단위 조회 조건 값들을 검색하는 방법으로 AND 또는 OR절로 검색해야 하는 경우

3. 시 단위 검색 조건

4. 동 단위 검색 조건

위 조건으로만 동적쿼리를 생성해봅니다. 시 단위, 동 단위도 추가적으로 할 수 있으나 처리 방법을 동일하기 때문입니다. 아래 쿼리는 방법을 기록하기 위한 것으로 전체적인 내용보다는 검색조건을 처리하는지에 중점을 두고 보기 바라며,

“전북,전남,경기도” 라는 검색 조건을 받은 후 이 값들을 질의할 때 AND 조건으로 검색하는 방법입니다.

declare @P_CUSTOMER_AREA				VARCHAR(30) = ''
DECLARE 	@P_SEARCH_STR		VARCHAR(3000) = ''

DECLARE 	@P_ST_INDEX			INT = 0
DECLARE     @P_EN_INDEX			INT = 0

DECLARE @P_SEARCH_AND_OR 	VARCHAR(1) 

DECLARE @P_DO_STR 	    VARCHAR(MAX) 
DECLARE @P_ROW_COUNT		INT				= 0	
set @P_CUSTOMER_AREA='SYSTEM'
SET @P_EN_INDEX = 10


DECLARE @YOUR_SIDO VARCHAR(30) 

-- and 조건으로 처리
SET @P_SEARCH_AND_OR ='A' 

DECLARE @QUERY_STR	 VARCHAR(MAX) = '' 
DECLARE @P_QUERY	 NVARCHAR(MAX) = ''; 

-- 도 단위 검색 조건
set @P_DO_STR ='전북,전남,경기도' 

IF @P_SEARCH_AND_OR = 'A'
BEGIN
	SELECT 
			@QUERY_STR = @QUERY_STR + ' AND CHARINDEX('''+ Value +''', a.DO_UNIT_NMS) > 0' 	  
	FROM [DBO].[FN_TBL_SPLIT_NVARCHAR](@P_DO_STR, ',')
END
ELSE IF @P_SEARCH_AND_OR = 'O'
BEGIN
	SELECT 
			@QUERY_STR = @QUERY_STR + ' OR CHARINDEX('''+ Value +''', a.DO_UNIT_NMS) > 0' 	  
	FROM [DBO].[FN_TBL_SPLIT_NVARCHAR](@P_DO_STR, ',')

	SET @QUERY_STR = 'AND ('+ SUBSTRING(@QUERY_STR,4,LEN(@QUERY_STR)) +')'
END	 
	 
  
SET @P_QUERY = '
		SELECT *
		FROM
		(
			SELECT	a.ADDRESS_ID,					
					TITLE_NM = c2.CODE_D_NM,
					(SELECT MAX(CONVERT(VARCHAR, CONVERT(DATE, ACT_DATE), 120)) FROM CUSTOMER_ACT v WHERE a.CUSTOMER_AREA = v.CUSTOMER_AREA AND a.ADDRESS_ID = v.ADDRESS_ID AND v.STATUS_CD = ''O'') AS ACT_DATE,
					STUFF((
						SELECT '',''+cp.DO_UNIT_NMS
						FROM CUSTOMER_ACT ca
						INNER JOIN CUSTOMER_ACT_PROD cap ON(ca.CUSTOMER_AREA = cap.CUSTOMER_AREA AND ca.ACT_ID = cap.ACT_ID)
						INNER JOIN CUSTOMER_PROD cp ON(cap.CUSTOMER_AREA = cp.CUSTOMER_AREA AND cap.PROD_ID = cp.PROD_ID)
						WHERE ca.CUSTOMER_AREA = a.CUSTOMER_AREA
						AND ca.ADDRESS_ID = a.ADDRESS_ID
						AND ca.STATUS_CD = ''O''
						AND ca.ACT_DATE = (SELECT MAX(ACT_DATE) FROM CUSTOMER_ACT v WHERE a.CUSTOMER_AREA = v.CUSTOMER_AREA AND a.ADDRESS_ID = v.ADDRESS_ID AND v.STATUS_CD = ''O'')
						FOR XML PATH('''')
					),1,1,'''') AS DO_UNIT_NMS,					
			FROM CUSTOMER_CONT a
			INNER JOIN CUSTOMER_WP b ON(a.CUSTOMER_AREA = b.CUSTOMER_AREA AND a.ADDRESS_ID = b.ADDRESS_ID)
			INNER JOIN CUSTOMER_COMP c ON(b.CUSTOMER_AREA = c.CUSTOMER_AREA AND b.COMPANY_ID = c.COMPANY_ID)
			LEFT JOIN CUSTOMER_WP_EMP d ON(b.CUSTOMER_AREA = d.CUSTOMER_AREA AND b.ADDRESS_ID = d.ADDRESS_ID AND b.COMPANY_ID = d.COMPANY_ID)
			LEFT JOIN CUSTOMER_DEPT_EMP e  ON(d.CUSTOMER_AREA = e.CUSTOMER_AREA AND d.USER_ID = e.USER_ID AND e.MAIN_DEPT_YN = ''Y'')
			OUTER APPLY [dbo].[FN_CODE_NM](''ADDRESS_SP'', a.ADDRESS_SP_CD, '''') c1
			WHERE a.CUSTOMER_AREA ='''+ @P_CUSTOMER_AREA+'''
			AND e.DEPT_ID = '''+@YOUR_SIDO+'''
			AND a.STATUS_CD = ''O''
			AND c.STATUS_CD = ''O''
		) a
		WHERE 1=1
		AND 
		(
			'''+@P_SEARCH_STR +'''= '''' OR 
			(
				a.ADDRESS_NM LIKE ''%'''+@P_SEARCH_STR+'''%''
				OR
				a.COMPANY_NM LIKE ''%'''+@P_SEARCH_STR+'''%''
				OR
				a.ADDRESS_SP_NM LIKE ''%'''+@P_SEARCH_STR+'''%''
				OR
				a.COMPANY_TEL_NO LIKE ''%'''+@P_SEARCH_STR+'''%''
				OR
				a.ADDRESS_MOBILE_NO LIKE ''%'''+@P_SEARCH_STR+'''%''
			)
		)
		AND ('''+@P_EXE_YN+''' = '''' OR a.USING_ITEMS > 0)
		'+@QUERY_STR+'
		ORDER BY a.COMPANY_NM, a.ADDRESS_NM
		OFFSET '+CONVERT(VARCHAR(10),@P_ST_INDEX)+' ROWS FETCH NEXT '+CONVERT(VARCHAR(10),@P_EN_INDEX)+' ROWS ONLY
		'

--동적으로 생성된 쿼리 확인
select @P_QUERY

--동적쿼리 실행
EXECUTE(@P_QUERY)

--동적쿼리 행수 반환 (페이징 처리를 위해)
SELECT @P_ROW_COUNT = @@ROWCOUNT;

하나씩 설명 들어갑니다.

1. 검색조건으로 전달받은 값을 임의로 설정하였습니다. @P_SEARCH_AND_OR의 값은 AND 조건으로 질의를 의미합니다.

set @P_DO_STR ='전북,전남,경기도' 

set @P_SEARCH_AND_OR = 'A'

2. AND 조건인지, OR 조건인지에 따라 동적쿼리 스트링을 생성합니다.

IF @P_SEARCH_AND_OR = 'A'
BEGIN
	SELECT 
			@QUERY_STR = @QUERY_STR + ' AND CHARINDEX('''+ Value +''', a.DO_UNIT_NMS) > 0' 	  
	FROM [DBO].[FN_TBL_SPLIT_NVARCHAR](@P_DO_STR, ',')
END
ELSE IF @P_SEARCH_AND_OR = 'O'
BEGIN
	SELECT 
			@QUERY_STR = @QUERY_STR + ' OR CHARINDEX('''+ Value +''', a.DO_UNIT_NMS) > 0' 	  
	FROM [DBO].[FN_TBL_SPLIT_NVARCHAR](@P_DO_STR, ',')

	SET @QUERY_STR = 'AND ('+ SUBSTRING(@QUERY_STR,4,LEN(@QUERY_STR)) +')'
END

위에서 생성된 쿼리의 결과값은 다음과 같습니다.

SELET @QUERY_STR

AND CHARINDEX('전북', a.DO_UNIT_NMS) > 0 
AND CHARINDEX('전남', a.DO_UNIT_NMS) > 0 
AND CHARINDEX('경기도', a.DO_UNIT_NMS) > 0

CHARINDEX()문을 사용한 이유는 하나입니다.  다음 쿼리 부분을 설명합니다. DO_UNIT_NMS 칼럼은 도단위 데이터를 STUFF함수를 사용하여 콤마(,)단위로 연결하여 하나의 문자열로 처리되고 있습니다.

값은 이런식으로 됩니다.

'전북,'전남',서울',경기' AS DO_UNIT_NMS

CHARINDEX()함수를 사용하여 해당 값이 존재하면 해당위치의 인덱스를 리턴합니다. 존재하지 않으면 -1를 리턴해요.

............생략
STUFF((
	SELECT '',''+cp.DO_UNIT_NMS
	FROM CUSTOMER_ACT ca
	INNER JOIN CUSTOMER_ACT_PROD cap ON(ca.CUSTOMER_AREA = cap.CUSTOMER_AREA AND ca.ACT_ID = cap.ACT_ID)
	INNER JOIN CUSTOMER_PROD cp ON(cap.CUSTOMER_AREA = cp.CUSTOMER_AREA AND cap.PROD_ID = cp.PROD_ID)
	WHERE ca.CUSTOMER_AREA = a.CUSTOMER_AREA
	AND ca.ADDRESS_ID = a.ADDRESS_ID
	AND ca.STATUS_CD = ''O''
	AND ca.ACT_DATE = (SELECT MAX(ACT_DATE) FROM CUSTOMER_ACT v WHERE a.CUSTOMER_AREA = v.CUSTOMER_AREA AND a.ADDRESS_ID = v.ADDRESS_ID AND v.STATUS_CD = ''O'')
	FOR XML PATH('''')
),1,1,'''') AS DO_UNIT_NMS,

.....생략

이렇게 만들어진 쿼리 문자열을 다음과 같이 쿼리문자열로 연결하고 있지요

......생략........
'+@QUERY_STR+'
ORDER BY a.COMPANY_NM, a.ADDRESS_NM

3.  SET @P_QUERY= 는 실행할 쿼리를 문자열로 만드는 과정입니다.

SET @P_QUERY = '.....................'생략

문자열로 쿼리를 만들 때, ‘정읍시’ 처럼 작은따옴표 값으로 묶어줘야 하는 경우 작은 따옴표가 2개로 묶어줍니다.

AND SIDO =''정읍시''

빈값인경우 처리시 ‘작은 따옴표 4개로 처리합니다.

AND SIDO =''''

파라미터 값으로 받은 문자를 문자열로 처리해야하는 경우에는 작은 따옴표 3개로 묶어서 처리합니다.

WHERE a.CUSTOMER_AREA ='''+ @P_CUSTOMER_AREA+'''

프로시저 생성시 선언한 변수가 INT 타입이라면 CONVERT함수로 형변환하여 문자열로 연결해주어야 오류발생을 피할 수 있습니다. 다음과 같이 프로시저에서 대부분 페이징 처리할 때 INT타입으로 선언하기 때문에 아래와 같이 처리해주세요.

OFFSET '+CONVERT(VARCHAR(10),@P_ST_INDEX)+' ROWS FETCH NEXT '+CONVERT(VARCHAR(10),@P_EN_INDEX)+' ROWS ONLY

4. 동적쿼리 실행은 EXECUTE문을 사용합니다.

EXECUTE(@P_QUERY)

5. 실행한 쿼리문의 총 행의 수를 리턴받는 방법은 @@ROWCOUNT 를 실행하여 처리합니다. 페이징 처리시 필요하기때문에 대부분 전달해요.

SELECT @P_ROW_COUNT = @@ROWCOUNT;

[참고]

위에서 생성한 동적 쿼리를 미리 실행해보기 위해 SELECT @P_QUERY 질의하면 다음과 같이 검색 조건 값들이 쿼리 질의문에 모두 삽입된 상태의 완성된 쿼리문의 결과를 얻습니다.

SELECT *
FROM
(
	SELECT	a.ADDRESS_ID,					
			TITLE_NM = c2.CODE_D_NM,
			(SELECT MAX(CONVERT(VARCHAR, CONVERT(DATE, ACT_DATE), 120)) FROM CUSTOMER_ACT v WHERE a.CUSTOMER_AREA = v.CUSTOMER_AREA AND a.ADDRESS_ID = v.ADDRESS_ID AND v.STATUS_CD = 'O') AS ACT_DATE,
			STUFF((
				SELECT ','+cp.DO_UNIT_NMS
				FROM CUSTOMER_ACT ca
				INNER JOIN CUSTOMER_ACT_PROD cap ON(ca.CUSTOMER_AREA = cap.CUSTOMER_AREA AND ca.ACT_ID = cap.ACT_ID)
				INNER JOIN CUSTOMER_PROD cp ON(cap.CUSTOMER_AREA = cp.CUSTOMER_AREA AND cap.PROD_ID = cp.PROD_ID)
				WHERE ca.CUSTOMER_AREA = a.CUSTOMER_AREA
				AND ca.ADDRESS_ID = a.ADDRESS_ID
				AND ca.STATUS_CD = 'O'
				AND ca.ACT_DATE = (SELECT MAX(ACT_DATE) FROM CUSTOMER_ACT v WHERE a.CUSTOMER_AREA = v.CUSTOMER_AREA AND a.ADDRESS_ID = v.ADDRESS_ID AND v.STATUS_CD = 'O')
				FOR XML PATH('')
			),1,1,'') AS DO_UNIT_NMS,					
	FROM CUSTOMER_CONT a
	INNER JOIN CUSTOMER_WP b ON(a.CUSTOMER_AREA = b.CUSTOMER_AREA AND a.ADDRESS_ID = b.ADDRESS_ID)
	INNER JOIN CUSTOMER_COMP c ON(b.CUSTOMER_AREA = c.CUSTOMER_AREA AND b.COMPANY_ID = c.COMPANY_ID)
	LEFT JOIN CUSTOMER_WP_EMP d ON(b.CUSTOMER_AREA = d.CUSTOMER_AREA AND b.ADDRESS_ID = d.ADDRESS_ID AND b.COMPANY_ID = d.COMPANY_ID)
	LEFT JOIN CUSTOMER_DEPT_EMP e  ON(d.CUSTOMER_AREA = e.CUSTOMER_AREA AND d.USER_ID = e.USER_ID AND e.MAIN_DEPT_YN = 'Y')
	OUTER APPLY [dbo].[FN_CODE_NM]('ADDRESS_SP', a.ADDRESS_SP_CD, '') c1
	WHERE a.CUSTOMER_AREA ='SYSTEM'
	AND e.DEPT_ID = 'a000'
	AND a.STATUS_CD = 'O'
	AND c.STATUS_CD = 'O'
) a
WHERE 1=1
AND 
(
	''= '' OR 
	(
		a.ADDRESS_NM LIKE '%''%'
		OR
		a.COMPANY_NM LIKE '%''%'
		OR
		a.ADDRESS_SP_NM LIKE '%''%'
		OR
		a.COMPANY_TEL_NO LIKE '%''%'
		OR
		a.ADDRESS_MOBILE_NO LIKE '%''%'
	)
)
AND ('Y' = '' OR a.USING_ITEMS > 0)
 AND CHARINDEX('전북', a.DO_UNIT_NMS) > 0 AND CHARINDEX('전남', a.DO_UNIT_NMS) > 0 AND CHARINDEX('경기도', a.DO_UNIT_NMS) > 0
ORDER BY a.COMPANY_NM, a.ADDRESS_NM
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

오늘은 여기까지!!