sp_executesql
여러 번 사용할 수 있거나, 동적으로 만들어진 Transact-SQL문 또는 일괄 처리를 실행합니다. Transact-SQL문 또는 일괄 처리는 포함 매개 변수를 포함할 수 있습니다.
구문
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
추가 매개 변수의 값에 대한 자리 표시자입니다. 값은 상수 또는 변수만 가능합니다. 값은 함수 또는 연산자를 사용하여 작성된 표현식 등과 같이, 보다 복잡한 표현식이 될 수 없습니다.
반환 코드 값
0(성공) 또는 1(실패)
결과 집합
SQL 문자열에 작성된 모든 SQL문에서 결과 집합을 반환합니다.
비고
sp_executesql은 일괄 처리, 이름의 범위 및 데이터베이스 컨텍스트면에서 EXECUTE와 동작이 동일합니다. sp_executesql stmt 매개 변수의 Transact-SQL문 또는 일괄 처리는 sp_executesql이 실행될 때까지 컴파일되지 않습니다. stmt의 내용은 sp_executesql이라는 일괄 처리의 실행 계획과 별도로 컴파일되고 실행됩니다. sp_executesql 일괄 처리는 sp_executesql을 호출하는 일괄 처리에서 선언된 변수를 참조할 수 없습니다. sp_executesql 일괄 처리의 로컬 커서 또는 변수는 sp_executesql을 호출하는 일괄 처리에는 보이지 않습니다. 데이터베이스 컨텍스트 내의 변경 사항은 sp_executesql문이 종료될 때까지만 지속됩니다.
문에 대한 매개 변수의 변경 사항이 변형뿐인 경우, Transact-SQL문을 여러 번 실행하기 위해 저장 프로시저 대신 sp_executesql을 사용할 수 있습니다. Transact-SQL문 자체에 상수가 남아 있으며 매개 변수 값만이 변경되었으므로 Microsoft® SQL Server™ 쿼리 최적화 프로그램이 첫번째 실행에 대해 생성된 실행 계획을 다시 사용할 확률이 높습니다.
참고
문의 문자열에 있는 개체 이름이 정식으로 규정되지 않은 경우에는 실행 계획이 다시 사용되지 않습니다.
sp_executesql은 Transact-SQL 문자열과 별도로 매개 변수 값의 설정을 지원합니다.
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
sp_executesql에서 매개 변수를 대치할 수 있는 경우에는 EXECUTE문을 사용하여 문자열을 실행하는 데 있어서 다음과 같은 장점이 있습니다.
- sp_executesql 문자열에 있는 Transact-SQL문의 실제 텍스트가 실행 사이에 변경되지 않으므로 쿼리 최적화 프로그램이 두 번째 실행의 Transact-SQL문과 첫번째 실행에 대해 생성된 실행 계획을 일치시킬 가능성이 있습니다. 따라서 SQL Server가 두 번째 문을 컴파일할 필요가 없습니다.
- Transact-SQL 문자열이 단 한 번만 작성됩니다.
- 정수 매개 변수는 자신의 기본 형식으로 지정됩니다. 유니코드를 캐스팅할 필요가 없습니다.
사용 권한
public 역할에 대한 기본 권한을 실행합니다.
예제
A. 단순 SELECT문 실행
다음은 @level이라는 포함 매개 변수를 포함한 단순 SELECT 문을 작성하고 실행하는 예제입니다.
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @level',
N'@level tinyint',
@level = 35
B. 동적으로 작성된 문자열 실행
다음은 sp_executesql을 사용하여 동적으로 작성된 문자열을 실행하는 예제입니다. 예로 든 저장 프로시저는 일 년 간의 판매 데이터를 파티션으로 분리하는데 사용되는 일련의 테이블에 데이터를 삽입하는 데 사용됩니다. 일 년의 각 달에는 다음과 같은 형식의 테이블이 한 개씩 있습니다.
CREATE TABLE May1998Sales
(OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATETIME NULL
CHECK (DATEPART(yy, OrderDate) = 1998),
OrderMonth INT
CHECK (OrderMonth = 5),
DeliveryDate DATETIME NULL,
CHECK (DATEPART(mm, OrderDate) = OrderMonth)
)
파티션으로 분리된 테이블에서 데이터를 검색하는 것에 관한 자세한 내용은 파티션으로 분리된 데이터로 보기 사용을 참조하십시오.
각 테이블의 이름은 달 이름의 첫 세 글자, 연도를 표시하는 네 자리 수 및 상수인 Sales로 구성됩니다. 이름은 주문 날짜에서 동적으로 작성될 수 있습니다.
/* Get the first three characters of the month name. */
SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
/* Concatenate the four-digit year; cast as character. */
CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
/* Concatenate the constant 'Sales'. */
'Sales'
이러한 견본 저장 프로시저는 동적으로 INSERT 문을 작성하고 실행하여 새 주문을 올바른 테이블에 삽입합니다. 또한 반드시 데이터를 포함해야 하는 테이블의 이름을 작성하는 데 주문 날짜를 사용하며, 작성된 이름은 INSERT 문에 병합됩니다. 이는 sp_executesql의 단순한 예입니다. 여기에는 오류 확인 및 테이블 간에 주문 번호가 중복되지 않았는지를 확인하는 등의 업무 규칙 확인이 포함되지 않습니다.
CREATE PROCEDURE InsertSales @PrmOrderID INT, @PrmCustomerID INT,
@PrmOrderDate DATETIME, @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString NVARCHAR(500)
DECLARE @OrderMonth INT
-- Build the INSERT statement.
SET @InsertString = 'INSERT INTO ' +
/* Build the name of the table. */
SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
'Sales' +
/* Build a VALUES clause. */
' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
' @InsOrdMonth, @InsDelDate)'
/* Set the value to use for the order month because
functions are not allowed in the sp_executesql parameter
list. */
SET @OrderMonth = DATEPART(mm, @PrmOrderDate)
EXEC sp_executesql @InsertString,
N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
@InsOrdMonth INT, @InsDelDate DATETIME',
@PrmOrderID, @PrmCustomerID, @PrmOrderDate,
@OrderMonth, @PrmDeliveryDate
GO
이 프로시저에서는 EXECUTE를 실행하는 것보다 sp_executesql을 실행하여 문자열을 실행하는 것이 더 효과적입니다. sp_executesql을 실행하는 경우, 각 개월별 테이블에 대해 한 개씩, 12 버전의 INSERT 문자열만이 생성됩니다. EXECUTE의 경우, 매개 변수 값이 다르므로 각 INSERT 문자열이 고유합니다. 두 가지 방법 모두 같은 수의 일괄 처리를 생성하지만 sp_executesql에 의해 생성된 INSERT 문자열의 유사성으로 인해 쿼리 최적화 프로그램이 실행 계획을 다시 사용할 확률이 높습니다.
