동적 쿼리의 해결사 sp_executesql vs. exec

Visual Basic, ASP, VB.NET에서 ADO, ADO.NET을 이용한 Database 프로그램을 개발하는 경우 프로그램내에 SQL 구문을 문자열로 연결한 후 실행하도록 구성한 코드를 많이 보게 된다.이러한 코드 구성을 일반적으로 '동적 쿼리', '하드 코딩된 쿼리'라 부른다.예를 들어 비주얼 베이직의 경우 Dim strSQL As StringstrSQL="select orderid, orderdate, employeeid from orders"strSQL=strSQL+" where orderid=" & txtOrderIDcnn.Execute strSQL 하지만 이같은 동적 쿼리는 가능한 사용하지 않는 것이 좋다. 대신 SQL Server의 저장 프로시저를 이용해 처리 용량과 응답 속도의 향상 및 유지 보수 등 저장 프로시저의 특징을 활용하는 것이 좋다.하지만 설계상의 문제나 또는 고객의 다양한 요구 구현 방법상의 이질적 문제로 인해 동적 쿼리를 써야하는 상황이 자주 발생한다. Q/A의 질문 유형 중에도 이같은 동적 쿼리 작성법에 관한 내용이 상당수를 차지하고 있다.저장 프로시저를 통해 코드를 구성하더라도 저장 프로시저 내에서 다시 동적 쿼리를 사용하게 되는 경우가 발생한다. 온라인 설명서에는 이를 '런타임 시 명령문 작성'이라는 주제로 설명했다. 이 경우 SQL Server에서는 sp_executesql, exec()의 두가지 명령을 사용할 수 있다.클라이언트나 서버 사이드에서 동적 쿼리를 사용해야 하는 상황이 되었을 때, 다시말해 저장 프로시저를 직접 사용할 수 없는 상황에서는 또 다른 해결 방법을 이용할 수 있다. 아래 두 가지 상황에서의 해결 방법이다.클라인트 사이드에서 동적 쿼리가 필요한 경우ADO, ADO.NET에서는 파라미터를 가진 동적 쿼리를 지정할 수 있으며 일반적으로 아래와 코드 구성을 가진다. 1. ADO, ODBC, Command 오브젝트 연동Dim strSQL As StringstrSQL="select orderid, orderdate, employeeid from orders"strSQL=strSQL+" where orderid = ?"2. ADO.NET, SqlClient 네임스페이스, SqlCommand 오브젝트와 연동Dim strSQL As StringstrSQL="select orderid, orderdate, employeeid from orders"strSQL=strSQL+" where orderid = @orderid"위에 코드를 실행하면 실제 SQL Server에서는 sp_executesql 시스템 프로시저를 통해서 실행이 된다. 특히 ADO.NET의 경우는 디자인 타임에 'SqlDataAdapter Configuration Wizard'를 사용하면 위와 같은 코드를 작성해 준다.SQL Server, 저장 프로시저에서 동적 쿼리가 필요한 경우exec() 아니라 sp_executesql 시스템 프로시저를 이용한다.결국 클라이언트 사이드건 서버 사이드건 sp_executesql이 사용된 것을 알 수 있다. exec()를 쓴 경우와 sp_executesql를 사용한 경우의 성능과 SQL Server의 Cache 매니저의 상황 비교에 대한 내용을 마지막에 추가해 두었다.일반적으로 sp_executesql은 exec()비해 몇 가지 장점을 제공한다.1. 쿼리문안에 매개변수(입력/출력)를 정의할 수 있다.2. 매개변수 사용으로 인해 쿼리 최적화 프로그램이 컴파일된 실행 플랜을 재 사용할 확률이 높아진다.실제로 exec()와 sp_executesql은 Cache 매니저의 처리 방법 및 활동 상태가 다르다는 것을 마지막에 추가한 성능 모니터링을 통해서 알 수가 있을 것이다.이번 기회의 sp_executesql 시스템 프로시저 다양한 사용법과 관련 지식을 얻는데 미력하나마 도움이 됐으면 한다. 그럼, 구문부터 살펴보자. sp_executesql [@stmt =] stmt[{, [@params =] N'@parameter_name data_type [,...n]' }{, [@param1 =] 'value1' [,...n] }]인수설명@stmt: T-SQL문 또는 배치 명령. ntext 형으로 변환될 수 있는 변수 또는 유니코드 상수 문자열. 내부에 @name 형식의 파라미터를 포함할 수 있다.@params: @stmt에 포함된 모든 파라미터의 이름과 데이터 타입을 정의한다.@param1: @params 파라미터에서 첫번째 파라미터에 할당할 값n : 각 파라미터에 대한 값을 할당한다.참고 UNICODE 문자열 상수를 지정할 때는 N'...'형식을 사용한다.sp_executesql 프로시저에 선언됐던 파라미터가 ntext형이기 때문에 문자열을 직접 지정할 때는 위와 같은 형태를 사용하면 된다.sp_executesql은 sql로 구성된 시스템 프로시저가 아니라 확장 프로시저이다.기본 예제 1. TOP 절의 값을 동적으로 지정하고자 하는 경우(아래 구문은 set rowcount n 세션 옵션으로 대치할 수도 있다)declare @cnt as nvarchar(5)declare @stmt as nvarchar(100)set @cnt = '5'set @stmt = 'select top ' + @cnt + ' * from northwind.dbo.orders' exec sp_executesql @stmt아래 예제는 단순히 SQL을 동적으로 작성하고자 하는 경우로 @db의 값이 실행 시마다다른 데이터베이스명이 올 수 있다고 가정한다. 테이블명은 동일한다.declare @db as nvarchar⑳declare @stmt as nvarchar(100)set @db = 'northwind'set @stmt = 'select * from '+ @db +'.dbo.orders' exec sp_executesql @stmt2. 하나 이상의 명령을 배치로 실행declare @stmt as nvarchar(500)set @stmt = 'use northwind; 'set @stmt = @stmt + 'select top 5 * from dbo.orders where orderid=10248; 'set @stmt = @stmt + 'select top 5 * from dbo.[order details] where orderid=10248' exec sp_executesql @stmt입력 파라미터를 적용한 예제 1. @orderid 입력 파라미터를 이용해 해당 주문 번호를 가진 [order details] 테이블의 주문 제품 정보를 출력use northwinddeclare @stmt as nvarchar(100)declare @params as nvarchar(100)set @stmt = 'select productid, quantity, unitprice from 'set @stmt = @stmt + 'dbo.[order details] where orderid=@orderid'set @params = '@orderid int' exec sp_executesql @stmt, @params, @orderid=102482. 위 예제를 저장 프로시저 안에서 연동한 경우use northwindcreate proc upOrderDetailsSel@porderid intasdeclare @stmt as nvarchar(100)declare @params as nvarchar(100)set @stmt = 'select productid, quantity, unitprice from 'set @stmt = @stmt + 'dbo.[order details] where orderid=@orderid'set @params = '@orderid int' exec sp_executesql @stmt, @params, @orderid=@porderidgoexec upOrderDetailsSel @porderid = 10248 input 파라미터를 적용한 예제 1. @table을 입력 파라미터를 이용해서 실제 입력할 대상 테이블을 결정 입력될 컬럼값들 또한 입력 파라미터로 처리한 예제-- 아래 테스트용 테이블을 우선 작성할 것.create table northwind.dbo.table1 (a int, b int, c int)use northwinddeclare @table as nvarchar⑳declare @stmt as nvarchar(100)declare @params as nvarchar(100)set @table = 'dbo.table1'set @stmt = 'insert into ' + @table + ' 'set @stmt = @stmt + 'values (@a, @b, @c)'set @params = '@a int, @b int, @c int' exec sp_executesql @stmt, @params, @a=1, @b=2, @c=3 확인 select * from table12 .output 파라미터를 적용한 예제 동적 SQL문 내에도 출력(OUTPUT) 파라미터를 선언하고 결과 값을 리턴 받을 수 있다. 저장 프로시저에서 출력 파라미터를 사용하는 것과 동일하게, 선언부와 호출부에 반드시 OUTPUT 키워드를 지정한다.use northwinddeclare @stmt as nvarchar(100)declare @params as nvarchar(100)declare @orderret as intset @stmt = 'select @ordercnt = count(*) from dbo.orders'set @params = '@ordercnt as int OUTPUT' -- OUTPUT 키워드에 주의여기도 OUTPUT 키워드를 지정한다.exec sp_executesql @stmt, @params, @ordercnt = @orderret OUTPUT확인 select @orderret지원이 안되는 것들은 다음과 같다*. sp_executesql은 배치, 저장 프로시저, 트리거처럼 한 배치로 실행된다.1.동적 SQL문에서는 RETURN 문을 사용할 수 없다2.로컬 변수를 액세스 할 수 없다 declare @chr char(3)set @chr = 'abc' sp_executesql N'PRINT @CharVariable'GO3. 현재 데이터베이스가 변경되지 않는다 use pubs go sp_executesql N'use northwind' select * from shippers -- shippers 테이블은 northwind에 있다. go전문 개발자및 관리자를 위한 추가 정보성능 비교만일, 여러분이 Windows NT/2000의 성능 모니터의 사용법을 알고 있다면 아래 3가지 사용 예에 대한 모니터를 수행하고 각각 Cache Manager상의 활동이 어떻게 다른지를 비교 해 보면 많은 도움이 될 것이다.아래에 성능 모니터에 모니터링할 관련 오브젝트 및 카운트을 적어 뒀다.성능 개체SQL Server:Cache Manager- 모든 카운터- 다음 목록에서 인스턴스 선택_TotalAdhoc Sql PlansExecution ContextProcedure Plans기타... (관심이 있다면) 참고3개의 방법을 개별적으로 테스트할 때, Procedure Cache상에 동일한 플랜이 재 사용되는 것을 방지하기 위해 DBCC FREEPROCCACHE 명령을 사용할 수 있다. 이 명령은 프로시저 캐시에서 모든 요소를 제거한다. 이 작업을 수행해야 Cache Object Counter가 늘어나는 것을 볼 수 있다. 그리고 현재 Cache된 Object에 대한 정보를 보고자 하는 경우 아래의 쿼리를 이용하면 된다. select * from master..syscacheobjectswhere dbid = db_id('northwind')1. 저장 프로시저 테스트용DROP PROC dbo.upOrderDetailsQueryCREATE PROC dbo.upOrderDetailsQuery@orderid intASselect productid, quantity, unitprice from dbo.[order details]where orderid = @orderidgoEXEC dbo.upOrderDetailsQuery @orderid = 102482. sp_executesqlUSE Northwinddeclare @stmt as nvarchar(100)declare @params as nvarchar(100)set @stmt = 'select productid, quantity, unitprice from 'set @stmt = @stmt + 'dbo.[order details] where orderid=@orderid'set @params = '@orderid int' exec sp_executesql @stmt, @params, @orderid=10248go3. EXEC() 사용USE Northwinddeclare @stmt as nvarchar(100)declare @orderid varchar(10)set @orderid = '10248'set @stmt = 'select productid, quantity, unitprice from 'set @stmt = @stmt + 'dbo.[order details] where orderid = '+ @orderid EXEC (@stmt)