동적쿼리 SP_EXECUTESQL를 이용한 OUTPUT 파라미터 적용 예제
한때 ASP.NET에서 ADO, ADO.NET을 이용한 데이터베이스 연동시 프로그램내 SQL 구문을 작성, 구성하는 코드를 많이 사용했었다.
이렇게 코드내에 구성된 Query를 일반적으로 '동적쿼리'라 하며, 이 동적쿼리는 가급적 사용하지 않는 것이 좋으며, 저장 프로시저(Stored Procedure)를 사용하는 것이 좋단다.
이렇 경우 몇가지 이득을 볼 수 있는데 그 내용은 아래와 같다.
1. 응답 속도 면에서 이득을 볼 수 있다.
2. 관리 및 유지보수가 용이하다.
하지만, 개발하다보면 부득이하게 동적쿼리를 사용할 일이 생기며, 특히 저장 프로시저 내에서 동적쿼리를 사용하는 경우가 종종 발생한다. 동적쿼리는 SP_EXECUTESQL, EXEC 두가지로 나눌 수 있다. EXEC보다 SP_EXECUTESQL 사용을 추천하므로 다음 예제는 SP_EXECUTESQL에 대해 언급하기로 한다.
(*SP_EXECUTESQL와 EXEC의 차이점은 구글링을 통해 확인)이번 예제는 동적쿼리를 사용으로 쿼리 수행 결과 특정값을 리턴받기 위함이다. 즉 동적 쿼리 SQL문 내에서 OUTPUT 파라미터를 선언하고 결과값을 리턴 받기 위함인데 일반적으로 저장 프로시저에서 OUTPUT 키워드를 사용하는 것과 동일하다. 아래 예제를 살펴보자.
Ex)예제는 사내 일부 Point로 Cash 전환 리스트를 조회하는 저장 프로시저(AD_CASHEXCH_AR_LST)로 했으며, OUTPUT은 2개를 선언하고 리턴 받고자 한다.
CREATE PROCEDURE [dbo].[AD_CASHEXCH_AR_LST]
@pi_intUserNo BIGINT=NULL,
@pi_strSYMD VARCHAR(8),
@pi_strEYMD VARCHAR(8),
@po_intRecordCnt INT OUTPUT, --OUTPUT 파라미터로 조회된 Record 수
@po_intTotalMoney BIGINT OUTPUT --OUTPUT 파라미터로 조회된 전환 금액
...
DECLARE @v_strSql NVARCHAR(4000)
DECLARE @v_strSqlWhere NVARCHAR(4000)
...
IF ISNULL(@pi_intUserNo,0) <> 0 BEGIN
SET @v_strSqlWhere = @v_strSqlWhere + ' AND USERNO = '+ CAST(@pi_intUserNo AS VARCHAR) + ' '
END
IF @v_intFlag = 1 BEGIN
SET @v_strSqlWhere = @v_strSqlWhere + ' AND YMD BETWEEN ''' + LEFT(@pi_strSYMD, 8) +''' AND '''+ LEFT(@pi_strEYMD, 8) +''' '
END
--뷰에서 @po_intRecordCnt, @po_intTotalMoney 획득 OUTPUT으로 대입
SET @v_strSql = ''
SET @v_strSql = @v_strSql + ' SELECT @po_intRecordCnt = COUNT(*), '
SET @v_strSql = @v_strSql + ' @po_intTotalMoney = ISNULL(SUM(EXCHAMT), 0) '
SET @v_strSql = @v_strSql + ' FROM VCASHEXCHTRANMST '
SET @v_strSql = @v_strSql + ' WHERE 1 = 1 '
SET @v_strSql = @v_strSql + @v_strSqlWhere
--OUTPUT 파라미터 지정
EXEC SP_EXECUTESQL @v_strSql, N'@po_intRecordCnt INT OUTPUT, @po_intTotalMoney BIGINT OUTPUT', @po_intRecordCnt OUTPUT, @po_intTotalMoney OUTPUT
--뷰에서 전환 검색 조건에 맞는 Point 전환 리스트 조회
SET ROWCOUNT @pi_intPageSize
SET @v_strSql = ''
SET @v_strSql = @v_strSql + ' SELECT '
SET @v_strSql = @v_strSql + ' TID, '
SET @v_strSql = @v_strSql + ' USERID, '
SET @v_strSql = @v_strSql + ' EXCHAMT, '
SET @v_strSql = @v_strSql + ' EXCHCOMM, '
SET @v_strSql = @v_strSql + ' YMD, '
SET @v_strSql = @v_strSql + ' USESTATE, '
SET @v_strSql = @v_strSql + ' REGDATE '
SET @v_strSql = @v_strSql + ' FROM VCASHEXCHTRANMST WITH(NOLOCK) '
SET @v_strSql = @v_strSql + ' WHERE 1 = 1 '
IF ISNULL(@v_intStartSeqNo,'') <> '' BEGIN
SET @v_strSql = @v_strSql + 'AND TID <= ' + CAST(@v_intStartSeqNo AS VARCHAR) + ' '
END
SET @v_strSql = @v_strSql + @v_strSqlWhere
SET @v_strSql = @v_strSql + 'ORDER BY TID DESC '
EXEC SP_EXECUTESQL @v_strSql
SQL_EXECUTESQL에서 RETURN문을 사용할 수 없으며, 로컬 변수 역시 사용할 수 없다. 결국 프로시저에서 입/출력 파라미터를 정의 관련 내용을 Query에 구성하고 SP_EXECUTESQL를 통해 실행에서 결과 값을 받아 냈다.
다음 포스트에는 실행시 실행 계획에 어떤 영향을 미치는지 함께 확인하고자 한다.
참조
http://www.zdnet.co.kr/news/news_view.asp?artice_id=00000010048022&type=det&re=
http://msdn.microsoft.com/ko-kr/library/ms175170.aspx