USE PUBS
--SP_EXECUTESQL 프로시서 사용법--
/*
구문
sp_executesql [@stmt =] stmt
[
{, [@params =] N'@parameter_name data_type [,...n]' }
{, [@param1 =] 'value1' [,...n] }
]
인수
[@stmt =] stmt
Transact-SQL문 또는 일괄 처리를 포함한 유니코드 문자열입니다.
stmt는 암시적으로 ntext로 변환될 수 있는 변수 또는 유니코드 상수여야 합니다.
+ 연산자로 두 개의 문자열을 연결한 식 등과 같은 보다 복잡한 유니코드식은 사용할 수 없습니다.
문자 상수도 사용할 수 없습니다. 상수가 지정된 경우에는 N이라는 접두사가 있어야 합니다.
예를 들어, N'sp_who'라는 유니코드 상수는 사용할 수 있지만 'sp_who'는 사용할 수 없습니다. 문
자열의 크기는 사용 가능한 데이터베이스 서버의 메모리의 용량에 따라서만 제한됩니다.
stmt는 변수 이름과 동일한 형식의 매개 변수를 포함할 수 있습니다. 예를 들면 다음과 같습니다.
N'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'
stmt에 포함된 각 매개 변수에는 @params 매개 변수 정의 목록과 매개 변수 값 목록 모두에 해당되는 항목이 있어야 합니다.
[@params =] N'@parameter_name data_type [,...n]'
stmt에 포함된 모든 매개 변수의 정의를 포함하는 하나의 문자열입니다. 문자열은 암시적으로 ntext로 변환될 수 있는 변수 또는 유니코드 상수여야 합니다. 각 매개 변수의 정의는 매개 변수 이름과 데이터 형식으로 구성됩니다. n은 추가 매개 변수 정의를 나타내는 자리 표시자입니다. stmt에서 지정된 모든 매개 변수는 반드시 @params에서 정의되어야 합니다. stmt의 Transact-SQL문 또는 일괄 처리에 매개 변수가 없는 경우에는 @params가 필요하지 않습니다. 이 매개 변수의 기본값은 NULL입니다.
[@param1 =] 'value1'
매개 변수 문자열에서 정의된 첫번째 매개 변수의 값입니다. 값은 상수 또는 변수가 될 수 있습니다. stmt에 포함된 모든 매개 변수에 대해 제공되는 매개 변수 값이 있어야 합니다. stmt의 Transact-SQL문 또는 일괄 처리에 매개 변수가 없는 경우에는 값이 필요하지 않습니다.
n
추가 매개 변수의 값에 대한 자리 표시자입니다. 값은 상수 또는 변수만 가능합니다. 값은 함수 또는 연산자를 사용하여 작성된 표현식 등과 같이, 보다 복잡한 표현식이 될 수 없습니다.
*/
--사용하는 예를 보면 다음과 같습니다. 인자가 없는 SQL문 실행
Declare @strSQL nvarchar(200) --우선 선언을 합니다.(유니코드 형식이라서 NVARCHAR 형으로 선언합니다. VARCHAR로 선언하면 에러가 납니다.)
Set @strSQL=N'Select * from titles' --SQL문을 strSQL에 대입합니다. 이때 유니코드화 할수 있는 N 키를 사용합니다.
execute sp_executesql @strSQL --프로시저를 생합니다.
Declare @SQL nvarchar(200) --쿼리의 값을 선언합니다.
Declare @ParmDefinition nvarchar(100) --인자의 형식을 선언합니다. 여기선 NVARCHAR(100)으로 선언했습니다. 숫자면 INT로 하면 되겠죠?
Declare @ret_titleid nvarchar(100) --책자 코드를 변수로 하기 위해 지정 하였습니다.
Set @SQL=N'Select * from titles where title_id=@title_id' --실제 책자 코드는..@title_id입니다. 나중에 @ret_titleid를 인자로 받습니다.
Set @ParmDefinition=N'@title_id varchar(80)' --직업 대입합니다.
Set @titleid='bu1032' --인자에도 대입을 합니다.
Execute sp_executesql @SQL, @ParmDefinition, --실행하면 끝.
@title_id = @titleid
go
--이 아래의 예를 MSDN의 예를 그대로 사용한 것입니다.
DECLARE @IntVariable INT
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
/* Build the SQL string once.*/
SET @SQLString =
N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint'
/* Execute the string with the first parameter value. */
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @IntVariable
/* Execute the same string with the second parameter value. */
SET @IntVariable = 32
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @IntVariable
/*
이 프로시저를 이용할 경우 프로시저에서 여러형태의 쿼리를 동적으로 생성할시
유효한 결과를 출력할수 있습니다.
다시 말하면 SQL문등을 IF문등의 제어하여 결과물을 유도할수 있다는 얘깁니다.
*/
예제)
Alter Proc GetTitle
@qty nvarchar(3),
@price nvarchar(20),
@stor_id nvarchar(4)
as
Declare @SQL nvarchar(200)
Set @SQL='Select T.title_id,T.title,T.price,S.stor_id,S.qty '
Set @SQL=@SQL + ' from Titles as T inner join Sales as S '
Set @SQL=@SQL + ' on T.title_id=S.title_id '
Set @SQL=@SQL + ' where S.qty > ' + @qty
if (@price != '')
begin
Set @SQL=@SQL + ' and T.price > ' + @price
end
if (@stor_id != '')
begin
Set @SQL=@SQL + ' and S.stor_id = ''' + @stor_id + '''' ----------------1)
end
Select @SQL
Execute Sp_executesql @SQL ------------------------------2)
go
/*
위 문장을 보면 무척 복잡해 보일수도 있다.
사실 복잡하기도 하다 위 문장을 이해 하기 위해서는
프로시저와 T-SQL문을 알고 있어야 한다.
실무에서는 이러한 문장이 빈번히 사용되지는 않지만..
꼭 사용해야 할 경우가 있다.
그럴때 한번을 위해서 위와 같은 문장이 유효한 것이다.
Declare 은 VB, ASP의 DIM과 같은 역활을 한다.
그냥 선언을 위해 사용된다.
그리고 각 선언들이 NVARCHAR 로 정의 된 이유는 위에서 설명 되어 졌다.
--(너무 불친절 한거 같아서 여기서 설명한다. sp_executesql은 유니코드로 변환 가능한 상태여야만
하기 때문에..nvarchar로 정의 되어 지며 int, money numeric 등을 따로 정의 할 필요는 없다.
어차피 sp_exeuctesql을 사용하지전에 문장이 숫자와 문자의 구분이 되어 지기 때문이다.
위 문장의 2) 대신 Select @SQL 로 바꾸어 보면 이유를 알수 있다.
*/
--Tips...
--SQL문에서 위에 2) 에서 ==> ' and S.stor_id = ''' + @stor_id + '''' 에서 혿따움표가 네개임을 알수 있다.
--왜~~ 4개여야만 하는가... 그건 문자임을 나타내는 '@stor_id' 로 나타내는 것과 같다.
--아래를 실행해 보자....
Select ''
--아무것도 나타나지 않을것이다.
Select ''''
--앗 혼따움표가 4개인데...결과는 하나가 나타났다..
Select ''''''
--이번엔 2개가 나타났다.
Select ''''''''
--이번엔 3개가 나타났다..
--이 처럼 Select 할때 혿따움표 2개가 하나를 나타내는 것이다...
--위 프로시저를 결과는 아래를 실행하면 알수 있다.
Exec GetTitle '1','10.00', '6380' -- qty가 1 이상이고, 10.00달라보다 큰가격의 상점 아이디 6380의 결과치를 가져올것이다.
--만약 상점 아이디가 6380 이 아닌 전부를 가져 오겠다면..
Exec GetTitle '1','10.00', ''-- 이를 실행하면 아래의 문장을 실행하는 것과 같다. if 문에서 @stor_id !='' 로서 걸려지기 때문이다.
Select T.title_id,T.title,T.price,S.stor_id,S.qty from Titles as T inner join Sales as S on T.title_id=S.title_id
where S.qty > 1 and T.price > 10.00
--이 두 문장(프로시저와 SQL 쿼리)는 똑같은 결과를 가져 온다.
이상 sp_executesql 문의 유용한 용법과 사용 설명이었습니다.
--by Vins~~~
