sp_executesql(Transact-SQL)

구문

sp_executesql [ @statement = ] statement
[ 
    { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' } 
     { , [ @param1 = ] 'value1' [ ,...n ] }
]

인수

• [ @statement = ] statement
  Transact-SQL 문 또는 일괄 처리를 포함한 유니코드 문자열입니다. statement는 유니코드 상수 또는 유니코드 변수여야 합니다. + 연산자로 두 문자열을 연결한 식처럼 더 복잡한 유니코드 식은 사용할 수 없습니다. 문자 상수도 사용할 수 없습니다. 유니코드 상수를 지정한 경우 접두사 N으로 시작해야 합니다. 예를 들어 유니코드 상수 **N'sp_who'**는 유효하지만 **'sp_who'**는 유효하지 않습니다. 문자열의 크기는 사용 가능한 데이터베이스 서버 메모리의 용량에 따라서만 제한됩니다. 64비트 서버에서는 문자열 크기가 nvarchar(max)의 최대 크기인 2GB로 제한됩니다.

  [!참고]

  stmt는 변수 이름과 동일한 형식의 매개 변수를 포함할 수 있습니다. 예를 들면 다음과 같습니다. N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'

  stmt에 포함된 각 매개 변수에는 @params 매개 변수 정의 목록과 매개 변수 값 목록 모두에 해당되는 항목이 있어야 합니다.

• [ @params = ] N'@parameter_namedata_type [ ,... n ] '
  stmt에 포함된 모든 매개 변수의 정의를 포함하는 하나의 문자열입니다. 문자열은 유니코드 상수 또는 유니코드 변수여야 합니다. 각 매개 변수의 정의는 매개 변수 이름과 데이터 형식으로 구성됩니다. n은 추가 매개 변수 정의를 나타내는 자리 표시자입니다. statement에 지정된 모든 매개 변수는 반드시 @params에 정의되어야 합니다. stmt의 Transact-SQL 문 또는 일괄 처리에 매개 변수가 없으면 @params가 필요 없습니다. 이 매개 변수의 기본값은 NULL입니다.

• [ @param1 = ] 'value1'
  매개 변수 문자열에 정의된 첫 번째 매개 변수의 값입니다. 값은 유니코드 상수 또는 유니코드 변수가 될 수 있습니다. stmt에 포함된 모든 매개 변수에 대해 제공되는 매개 변수 값이 있어야 합니다. stmt의 Transact-SQL 문 또는 일괄 처리에 매개 변수가 없으면 값이 필요 없습니다.

• [ OUT | OUTPUT ]
  매개 변수가 출력 매개 변수임을 나타냅니다. 프로시저가 CLR(공용 언어 런타임) 프로시저가 아닐 경우 text, ntext 및 image 매개 변수를 OUTPUT 매개 변수로 사용할 수 있습니다. 프로시저가 CLR 프로시저가 아닐 경우 OUTPUT 키워드를 사용하는 출력 매개 변수가 커서 자리 표시자일 수 있습니다.

• n
  추가 매개 변수의 값에 대한 자리 표시자입니다. 값은 상수 또는 변수만 가능합니다. 함수 또는 연산자를 사용하여 작성한 식처럼 더 복잡한 식은 값으로 사용할 수 없습니다.

반환 코드 값

0(성공) 또는 0이 아닌 값(실패)

결과 집합

작성된 모든 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 문이 종료될 때까지만 지속됩니다.

문에 대한 매개 변수 값의 변경 내용이 변형뿐인 경우 저장 프로시저 대신 sp_executesql을 사용하여 Transact-SQL 문을 여러 번 실행할 수 있습니다. Transact-SQL 문 자체는 달라지지 않고 매개 변수 값만 달라지므로 SQL Server 쿼리 최적화 프로그램은 처음 실행할 때 생성되는 실행 계획을 다시 사용합니다.

다음 예와 같이 sp_executesql은 Transact-SQL 문자열과 별도로 매개 변수 값 설정을 지원합니다.

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2008R2.HumanResources.Employee 
       WHERE BusinessEntityID = @BusinessEntityID';
SET @ParmDefinition = N'@BusinessEntityID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @BusinessEntityID = @IntVariable;

출력 매개 변수는 sp_executesql에도 사용할 수 있습니다. 다음 예에서는 AdventureWorks2008R2.HumanResources.Employee 테이블에서 직함을 검색하여 출력 매개 변수 @max_title에 반환합니다.

DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle) 
   FROM AdventureWorks2008R2.HumanResources.Employee
   WHERE BusinessEntityID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;

sp_executesql에서 매개 변수를 대체할 수 있으면 EXECUTE 문을 사용하여 문자열을 실행할 때 다음과 같은 이점이 있습니다.

• sp_executesql 문자열에 있는 Transact-SQL 문의 실제 텍스트는 모든 실행에서 동일하기 때문에 쿼리 최적화 프로그램이 두 번째 실행의 Transact-SQL 문과 첫 번째 실행에서 생성된 실행 계획을 일치시킬 수 있습니다. 따라서 SQL Server에서 두 번째 문을 컴파일할 필요가 없습니다.

• Transact-SQL 문자열이 한 번만 작성됩니다.

• 정수 매개 변수는 해당 네이티브 형식으로 지정됩니다. 유니코드로 캐스팅할 필요가 없습니다.

사용 권한

public 역할의 멤버 자격이 필요합니다.

예

1. 단순 SELECT 문 실행

EXECUTE sp_executesql 
          N'SELECT * FROM AdventureWorks2008R2.HumanResources.Employee 
          WHERE BusinessEntityID = @level',
          N'@level tinyint',
          @level = 109;

2. 동적으로 작성된 문자열 실행

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)
    )

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

3. OUTPUT 매개 변수 사용

USE AdventureWorks2008R2;
GO
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @SalesOrderNumber nvarchar(25);
DECLARE @IntVariable int;
SET @SQLString = N'SELECT @SalesOrderOUT = MAX(SalesOrderNumber)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID';
SET @ParmDefinition = N'@CustomerID int,
    @SalesOrderOUT nvarchar(25) OUTPUT';
SET @IntVariable = 22276;
EXECUTE sp_executesql
    @SQLString
    ,@ParmDefinition
    ,@CustomerID = @IntVariable
    ,@SalesOrderOUT = @SalesOrderNumber OUTPUT;
-- This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;
-- This SELECT statement uses the value of the OUTPUT parameter in
-- the WHERE clause.
SELECT OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderNumber = @SalesOrderNumber;

참고 항목

참조

개념