동적쿼리 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