[펌]http://blog.daum.net/turnnig-pointer/16498100
최근 MS-SQL의 저장 프로시저를 쓰는일이 잦아졌다.
성능상의 문제도 그렇고, 보안상의 문제도 일반 쿼리문 전송보다는 저장 프로시저를 이용하는 것이
더욱 효과적이며, 안전하다고 알려져 있기때문이다.
물론 이제 새내기인지라 자세히는 모르지만
동적쿼리를 이용하면 일단 MS-SQL상의 실행계획이라는 것이 만들어져 계속해서 쿼리문을 날리는 것보다는 훨씬 효율적이라고 한다.
보안 문제 또한 요즘 극성인 인젝션 공격을 방어하기 위해서 저장 프로시저가 효율적이라고 한다.
자세한 내용은 "mssql 저장 프로시저 장점" 정도로 검색하면 이에 대한 정보를 찾을 수 있을것이다.
각설하고, 회사에서 계획중인 프로젝트의 샘플 게시판이 일반 쿼리문 전송 형태로 되어있었는데,
이를 전부 저장프로시저로 바꾸는 작업을 했다.
가장 마지막으로 저장 프로시저를 공부했던것이 대학교 1학년 때였으니까 햇수로는 약 3년전이다.
이 작업을 하면서 동적 쿼리를 작성하게 됐는데, 처음에는 자세히 몰라서 요구 작업상의 쿼리를 전부다 문자열로 변환하여 프로시저 내에서 실행하는 형태로 작업을 했다.
그러던중 text 형식의 구문을 실행하기 위해서는 막대한 길이의 nvarchar가 필요했고,
MS-SQL에서는 할당할 수 있는 길이의 한계가 정해져 있었다.
그를 해결하기 위해서 필요한게 동적 쿼리였다.
CREATE PROC [dbo].u_sp_insert
@board_id varchar(128),
@writer varchar(24),
@pwd varchar(24),
@content text,
AS
BEGIN
DECLARE
@strSql nvarchar(128),
@param nvarchar(128)
SET @strSql = 'INSERT INTO ' + @board_id + '(writer, [password], content)
VALUES(@p_writer, @p_password, @p_content)'
SET @param = '@p_writer varchar(24),
@p_password varchar(24),
@p_content text'
EXECUTE sp_executesql @strSql, @param,
@p_writer = @writer,
@p_password = @pwd,
@p_content = @content
END
이런식으로 작성하게 되면 임시로 생성한 쿼리문(동적 쿼리)의 길이(nvarchar(126))를 인수의 길이까지 신경쓰지 않아도 된다. 즉 @strSql 변수의 길이는 딱 그 쿼리문의 길이만 되면 된다.
이처럼 파라미터 값을 이용하여 동적쿼리를 생성하면 되는데, 만약 동적쿼리를 이용하여 특정한 값을 받고 싶을때는 OUTPUT 키워드를 이용하면 된다.
SET @strSql = 'SELECT @p_value = MAX(idx) from ' + @board_id + ' WHERE ref = @p_wheredata'
SET @params = '@p_wheredata int,
@p_value as int OUTPUT '
EXEC sp_executesql @strSql, @params,
@p_value = @value OUTPUT,
@p_wheredata = @wheredata
OUTPUT 키워드를 사용하면 동적쿼리를 이용해서 데이터를 받아올 수 있다.
[펌]http://database.sarang.net/?inc=read&aid=4074&criteria=mssql&subcrit=&id=&limit=20&keyword=%C5%D7%C0%CC%BA%ED%B8%ED+%BA%AF%BC%F6&page=1
--테이블을 변수로 지정하고싶은데 어떻게 지정해야하나요.
--INSERT 수강 VALUES (2006,3007,57,GETDATE())<-보통 이렇게작성하는데요.
--set @table = 'testdb1' <-테이블명을 변수로설정
--INSERT @table VALUES (2006,3007,57,GETDATE())<- 이렇게작성하면 에러가나요.
--혹시 가능하면 방법좀알려주세요~
--테이블 명을 변수로 처리할수 없습니다. 동적 쿼리를 이용하셔야 겠네요~
--declare @table sysname
--set @table = 'testdb1' --테이블명을변수로설정
--exec('insert ' + @table + ' values (2006,3007,57,getdate())')
[펌]http://database.sarang.net/?inc=read&aid=506&criteria=mssql&subcrit=&id=&limit=20&keyword=%C5%D7%C0%CC%BA%ED%B8%ED+%BA%AF%BC%F6&page=1
[원본]http://www.zdnet.co.kr/ArticleView.asp?artice_id=00000010048022
동적 쿼리의 해결사 sp_executesql vs. exec
김정선 (데브피아)
2002/04/13
Visual Basic, ASP, VB.NET에서 ADO, ADO.NET을 이용한 Database 프로그램을 개발하는 경우 프로그램내에 SQL 구문을 문자열로 연결한 후 실행하도록 구성한 코드를 많이 보게 된다.
이러한 코드 구성을 일반적으로 '동적 쿼리', '하드 코딩된 쿼리'라 부른다.
예를 들어 비주얼 베이직의 경우
Dim strSQL As String
strSQL="select orderid, orderdate, employeeid from orders"
strSQL=strSQL+" where orderid=" & txtOrderID
cnn.Execute strSQL
하지만 이같은 동적 쿼리는 가능한 사용하지 않는 것이 좋다. 대신 SQL Server의 저장 프로시저를 이용해 처리 용량과 응답 속도의 향상 및 유지 보수 등 저장 프로시저의 특징을 활용하는 것이 좋다.
하지만 설계상의 문제나 또는 고객의 다양한 요구 구현 방법상의 이질적 문제로 인해 동적 쿼리를 써야하는 상황이 자주 발생한다. Q/A의 질문 유형 중에도 이같은 동적 쿼리 작성법에 관한 내용이 상당수를 차지하고 있다.
저장 프로시저를 통해 코드를 구성하더라도 저장 프로시저 내에서 다시 동적 쿼리를 사용하게 되는 경우가 발생한다. 온라인 설명서에는 이를 '런타임 시 명령문 작성'이라는 주제로 설명했다. 이 경우 SQL Server에서는 sp_executesql, exec()의 두가지 명령을 사용할 수 있다.
클라이언트나 서버 사이드에서 동적 쿼리를 사용해야 하는 상황이 되었을 때, 다시말해 저장 프로시저를 직접 사용할 수 없는 상황에서는 또 다른 해결 방법을 이용할 수 있다.
아래 두 가지 상황에서의 해결 방법이다.
클라인트 사이드에서 동적 쿼리가 필요한 경우
ADO, ADO.NET에서는 파라미터를 가진 동적 쿼리를 지정할 수 있으며 일반적으로 아래와 코드 구성을 가진다.
1. ADO, ODBC, Command 오브젝트 연동
Dim strSQL As String
strSQL="select orderid, orderdate, employeeid from orders"
strSQL=strSQL+" where orderid = ?"
2. ADO.NET, SqlClient 네임스페이스, SqlCommand 오브젝트와 연동
Dim strSQL As String
strSQL="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(20)
declare @stmt as nvarchar(100)
set @db = 'northwind'
set @stmt = 'select * from '+ @db +'.dbo.orders'
exec sp_executesql @stmt
2. 하나 이상의 명령을 배치로 실행
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 northwind
declare @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=10248
2. 위 예제를 저장 프로시저 안에서 연동한 경우
use northwind
create proc upOrderDetailsSel
@porderid int
as
declare @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=@porderid
go
exec upOrderDetailsSel @porderid = 10248
input 파라미터를 적용한 예제
1. @table을 입력 파라미터를 이용해서 실제 입력할 대상 테이블을 결정 입력될 컬럼값들 또한 입력 파라미터로 처리한 예제
-- 아래 테스트용 테이블을 우선 작성할 것.
create table northwind.dbo.table1 (a int, b int, c int)
use northwind
declare @table as nvarchar(20)
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 table1
2 .output 파라미터를 적용한 예제
동적 SQL문 내에도 출력(OUTPUT) 파라미터를 선언하고 결과 값을 리턴 받을 수 있다. 저장 프로시저에서 출력 파라미터를 사용하는 것과 동일하게, 선언부와 호출부에 반드시 OUTPUT 키워드를 지정한다.
use northwind
declare @stmt as nvarchar(100)
declare @params as nvarchar(100)
declare @orderret as int
set @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'
GO
3. 현재 데이터베이스가 변경되지 않는다
use pubs
go
sp_executesql N'use northwind'
select * from shippers -- shippers 테이블은 northwind에 있다.
go
전문 개발자및 관리자를 위한 추가 정보
성능 비교
만일, 여러분이 Windows NT/2000의 성능 모니터의 사용법을 알고 있다면 아래 3가지 사용 예에 대한 모니터를 수행하고 각각 Cache Manager상의 활동이 어떻게 다른지를 비교 해 보면 많은 도움이 될 것이다.
아래에 성능 모니터에 모니터링할 관련 오브젝트 및 카운트을 적어 뒀다.
성능 개체
SQL Server:Cache Manager
- 모든 카운터
- 다음 목록에서 인스턴스 선택
_Total
Adhoc Sql Plans
Execution Context
Procedure Plans
기타... (관심이 있다면)
참고
3개의 방법을 개별적으로 테스트할 때, Procedure Cache상에 동일한 플랜이 재 사용되는 것을 방지하기 위해
DBCC FREEPROCCACHE
명령을 사용할 수 있다. 이 명령은 프로시저 캐시에서 모든 요소를 제거한다. 이 작업을 수행해야 Cache Object Counter가 늘어나는 것을 볼 수 있다. 그리고 현재 Cache된 Object에 대한 정보를 보고자 하는 경우 아래의 쿼리를 이용하면 된다.
select * from master..syscacheobjects
where dbid = db_id('northwind')
1. 저장 프로시저 테스트용
DROP PROC dbo.upOrderDetailsQuery
CREATE PROC dbo.upOrderDetailsQuery
@orderid int
AS
select productid, quantity, unitprice from dbo.[order details]
where orderid = @orderid
go
EXEC dbo.upOrderDetailsQuery @orderid = 10248
2. sp_executesql
USE Northwind
declare @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=10248
go
3. EXEC() 사용
USE Northwind
declare @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)