sp_executesql(Transact-SQL)

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System(PDW)Microsoft Fabric의 SQL 분석 엔드포인트Microsoft Fabric의 Warehouse

여러 번 다시 사용할 수 있는 Transact-SQL 문 또는 일괄 처리 또는 동적으로 빌드된 문을 실행합니다. Transact-SQL 문 또는 일괄 처리에는 포함된 매개 변수가 포함될 수 있습니다.

Transact-SQL 구문 표기 규칙

구문

SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics 및 PDW(Analytics Platform System)에 대한 구문입니다.

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

이 문서의 Transact-SQL 코드 샘플은 Microsoft SQL Server 샘플 및 커뮤니티 프로젝트 홈페이지에서 다운로드할 수 있는 샘플 데이터베이스를 사용합니다AdventureWorks2022.

인수

[ @stmt = ] N'statement'

Transact-SQL 문 또는 일괄 처리가 포함된 유니코드 문자열입니다. @stmt 유니코드 상수 또는 유니코드 변수여야 합니다. 두 문자열 + 을 연산자로 연결하는 것과 같이 더 복잡한 유니코드 식은 허용되지 않습니다. 문자 상수는 허용되지 않습니다. 유니코드 상수는 접두사로 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 은 더 많은 매개 변수 정의를 나타내는 자리 표시자입니다. @stmt 지정된 모든 매개 변수는 @params 정의해야 합니다. @stmt Transact-SQL 문 또는 일괄 처리에 매개 변수가 포함되어 있지 않으면 @params 필요하지 않습니다. 이 매개 변수의 기본값은 NULL입니다.

[ @param1 = ] 'value1'

매개 변수 문자열에 정의된 첫 번째 매개 변수의 값입니다. 값은 유니코드 상수 또는 유니코드 변수가 될 수 있습니다. @stmt 포함된 모든 매개 변수에 대해 매개 변수 값이 제공되어야 합니다. @stmt Transact-SQL 문 또는 일괄 처리에 매개 변수가 없는 경우에는 값이 필요하지 않습니다.

{ OUT | OUTPUT }

매개 변수가 출력 매개 변수임을 나타냅니다. 프로시저가 CLR(공용 언어 런타임) 프로시저가 아니면 텍스트, ntext 및 이미지 매개 변수를 매개 변수로 OUTPUT 사용할 수 있습니다. 프로시저가 OUTPUT CLR 프로시저가 아닌 한 키워드(keyword) 사용하는 출력 매개 변수는 커서 자리 표시자가 될 수 있습니다.

[ ... n ]

추가 매개 변수 값에 대한 자리 표시자입니다. 값은 상수 또는 변수일 수 있습니다. 값은 함수와 같은 더 복잡한 식이나 연산자를 사용하여 작성된 식일 수 없습니다.

반환 코드 값

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

결과 집합

작성된 모든 SQL 문에서 SQL 문자열로 결과 집합을 반환합니다.

설명

sp_executesql매개 변수는 이 문서의 앞부분에 있는 구문 섹션에 설명된 대로 특정 순서로 입력해야 합니다. 매개 변수를 순서대로 입력하면 오류 메시지가 발생합니다.

sp_executesql 에는 일괄 처리, 이름 범위 및 데이터베이스 컨텍스트와 동일한 동작 EXECUTE 이 있습니다. @stmt 매개 변수의 sp_executesqlTransact-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 once */
SET @SQLString = N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2022.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사용할 수도 있습니다. 다음 예제에서는 샘플 데이터베이스의 HumanResources.Employee 테이블에서 작업 제목을 AdventureWorks2022 검색하고 출력 매개 변수 @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 AdventureWorks2022.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 역할의 멤버 자격이 필요합니다.

예제

A. SELECT 문 실행

다음 예제에서는 명명@level된 매개 변수가 SELECT 포함된 문을 만들고 실행합니다.

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

B. 동적으로 빌드된 문자열 실행

다음 예제에서는 동적으로 빌드된 문자열을 실행하는 데 사용하는 sp_executesql 방법을 보여 줍니다. 저장 프로시저 예제는 1년 동안 판매 데이터를 분할하는 데 사용되는 테이블 집합에 데이터를 삽입하는 데 사용됩니다. 다음 형식의 연중 각 달에 대해 하나의 테이블이 있습니다.

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

이 샘플 저장 프로시저는 문을 동적으로 빌드하고 실행 INSERT 하여 올바른 테이블에 새 주문을 삽입합니다. 이 예제에서는 주문 날짜를 사용하여 데이터를 포함해야 하는 테이블의 이름을 빌드한 다음 해당 이름을 문에 INSERT 통합합니다.

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

이 절차에서 사용하는 sp_executesql 것이 문자열을 실행하는 데 사용하는 EXECUTE 것보다 더 효율적입니다. sp_executesql 사용되는 경우 생성된 문자열의 INSERT 버전은 12개뿐이며, 각 월별 테이블에 대해 하나씩만 생성됩니다. 매개 EXECUTE변수 값이 다르기 때문에 각 INSERT 문자열은 고유합니다. 두 메서드 모두 동일한 수의 일괄 처리를 생성하지만, 생성된 sp_executesql 문자열의 INSERT 유사성으로 인해 쿼리 최적화 프로그램에서 실행 계획을 다시 사용할 가능성이 높아집니다.

C. OUTPUT 매개 변수 사용

다음 예제에서는 매개 변수를 OUTPUT 사용하여 문에 의해 생성된 결과 집합을 SELECT 매개 변수에 @SQLString 저장합니다. 그런 다음 매개 변수 값을 OUTPUT 사용하는 두 개의 SELECT 문이 실행됩니다.

USE AdventureWorks2022;
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;

예: Azure Synapse Analytics 및 분석 플랫폼 시스템(PDW)

D. SELECT 문 실행

다음 예제에서는 명명@level된 매개 변수가 SELECT 포함된 문을 만들고 실행합니다.

EXECUTE sp_executesql
    N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee
    WHERE EmployeeKey = @level',
    N'@level TINYINT',
    @level = 109;

관련 콘텐츠

• EXECUTE(Transact-SQL)
• 시스템 저장 프로시저(Transact-SQL)