sp_executesql(Transact-SQL)

아티클
11/02/2024

적용 대상: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW) Microsoft Fabric의 SQL 분석 엔드포인트 Microsoft Fabric의 웨어하우스

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

주의

런타임 컴파일 Transact-SQL 문은 애플리케이션을 악의적인 공격에 노출할 수 있습니다. 를 사용할 sp_executesql때 쿼리를 매개 변수화해야 합니다. 자세한 내용은 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 코드 샘플은 AdventureWorks2022 또는 AdventureWorksDW2022 샘플 데이터베이스를 사용하며, 이는 Microsoft SQL Server 예시 및 커뮤니티 프로젝트(Microsoft SQL Server Samples and Community Projects) 홈 페이지에서 다운로드할 수 있습니다.

인수

[ @stmt = ] N'statement'

Transact-SQL 문 또는 일괄 처리가 포함된 UNICODE 문자열입니다. @stmt는 UNICODE 상수 또는 UNICODE 변수여야 합니다. + 연산자를 사용한 두 문자열 연결 등의 더 복잡한 유니코드 식은 허용되지 않습니다. 문자 상수는 허용되지 않습니다. 유니코드 상수는 접두사로 N와야 합니다. 예를 들어 UNICODE 상수 N'sp_who'는 유효하지만 문자 상수 'sp_who'는 유효하지 않습니다. 문자열의 크기는 사용 가능한 데이터베이스 서버 메모리의 용량에 따라서만 제한됩니다. 64비트 서버에서 문자열의 크기는 nvarchar(max)의 최대 크기인 2GB로 제한됩니다.

@stmt 변수 이름과 형식이 같은 매개 변수를 포함할 수 있습니다. 예시:

N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter';

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

[ @params = ] N'@parameter_name data_type [ , ...n ]'

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

[ @param1 = ] 'value1'

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

{ OUT | OUTPUT }

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

[ ... n ]

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

반환 코드 값

0(성공) 또는 0이 아닌 값(실패)입니다.

결과 집합

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

설명

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

sp_executesql 에는 일괄 처리, 이름 범위 및 데이터베이스 컨텍스트와 동일한 동작 EXECUTE 이 있습니다. @stmt 매개 변수의 Transact-SQL 문 또는 일괄 처리 sp_executesql 는 문이 실행될 때까지 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 AS INT;
DECLARE @SQLString AS NVARCHAR (500);
DECLARE @ParmDefinition AS 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 AS INT;

DECLARE @SQLString AS NVARCHAR (500);

DECLARE @ParmDefinition AS NVARCHAR (500);

DECLARE @max_title AS 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 문자열은 한 번만 빌드됩니다.
정수 매개 변수는 해당 네이티브 형식으로 지정됩니다. 유니코드로 캐스팅할 필요는 없습니다.

OPTIMIZED_SP_EXECUTESQL

적용 대상: Azure SQL Database

OPTIMIZED_SP_EXECUTESQL 데이터베이스 범위 구성 을 사용하도록 설정하면 사용하여 sp_executesql 제출된 일괄 처리의 컴파일 동작은 저장 프로시저 및 트리거와 같은 개체가 현재 사용하는 직렬화된 컴파일 동작과 동일합니다.

일괄 처리가 동일한 경우(매개 변수 차이 제외) OPTIMIZED_SP_EXECUTESQL 옵션은 컴파일 프로세스가 serialize되도록 보장하기 위해 적용 메커니즘으로 컴파일 잠금을 가져오려고 합니다. 이 잠금은 여러 세션이 동시에 호출 sp_executesql 되는 경우 첫 번째 세션이 컴파일 프로세스를 시작한 후 배타적인 컴파일 잠금을 가져오는 동안 해당 세션이 대기하도록 합니다. 컴파일의 sp_executesql 첫 번째 실행은 컴파일된 계획을 계획 캐시에 삽입합니다. 다른 세션은 컴파일 잠금 대기를 중단하고 사용 가능해지면 계획을 다시 사용합니다.

OPTIMIZED_SP_EXECUTESQL 이 옵션을 사용하지 않으면 컴파일을 통해 sp_executesql 실행되는 동일한 일괄 처리의 여러 호출이 병렬로 실행되고 컴파일된 계획의 자체 복사본을 계획 캐시에 배치하여 경우에 따라 계획 캐시 항목을 바꾸거나 복제합니다.

참고 항목

데이터베이스 범위 구성을 OPTIMIZED_SP_EXECUTESQL 사용하도록 설정하기 전에 자동 업데이트 통계를 사용하는 경우 ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY 데이터베이스 범위 구성 옵션을 사용하여 통계 비동기 자동 업데이트 옵션을 사용하도록 설정해야 합니다. 이러한 두 옵션을 사용하면 과도한 잠금 관리자 전용 잠금(LCK_M_X) 및 대기와 WAIT_ON_SYNC_STATISTICS_REFRESH 함께 긴 컴파일 시간과 관련된 성능 문제가 발생할 가능성을 크게 줄일 수 있습니다.

OPTIMIZED_SP_EXECUTESQL 는 기본적으로 꺼져 있습니다. 데이터베이스 수준에서 사용하도록 설정 OPTIMIZED_SP_EXECUTESQL 하려면 다음 Transact-SQL 문을 사용합니다.

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZED_SP_EXECUTESQL = ON;

사용 권한

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 통합합니다.

참고 항목

이 예제는 .에 대한 기본 예제입니다 sp_executesql. 이 예제에는 오류 검사가 포함되지 않으며, 주문 번호가 테이블 간에 중복되지 않도록 보장하는 등 비즈니스 규칙에 대한 검사를 포함하지 않습니다.

CREATE PROCEDURE InsertSales @PrmOrderID INT,
    @PrmCustomerID INT,
    @PrmOrderDate DATETIME,
    @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString AS NVARCHAR (500);
DECLARE @OrderMonth AS 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 수 있으므로 동적으로 빌드된 문자열을 실행하는 것보다 더 효율적입니다. 매개 변수 표식을 사용하면 데이터베이스 엔진 생성된 쿼리 계획을 다시 사용할 가능성이 높아 추가 쿼리 컴파일을 방지할 수 있습니다. 매개 EXECUTE변수 값이 다르므로 각 INSERT 문자열은 고유하며 동적으로 생성된 문자열의 끝에 추가됩니다. 실행하면 계획 재사용을 권장하는 방식으로 쿼리가 매개 변수화되지 않으며 각 INSERT 문이 실행되기 전에 컴파일되어야 하며, 그러면 계획 캐시에 쿼리의 캐시된 별도의 항목이 추가됩니다.

C. OUTPUT 매개 변수 사용

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

USE AdventureWorks2022;
GO

DECLARE @SQLString AS NVARCHAR (500);
DECLARE @ParmDefinition AS NVARCHAR (500);
DECLARE @SalesOrderNumber AS NVARCHAR (25);
DECLARE @IntVariable AS 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;

다음을 통해 공유

sp_executesql(Transact-SQL)

구문

인수

[ @stmt = ] N'statement'

[ @params = ] N'@parameter_name data_type [ , ...n ]'

[ @param1 = ] 'value1'

{ OUT | OUTPUT }

[ ... n ]

반환 코드 값

결과 집합

설명

OPTIMIZED_SP_EXECUTESQL

사용 권한

예제

A. SELECT 문 실행

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

C. OUTPUT 매개 변수 사용

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

D. SELECT 문 실행

피드백

추가 리소스

다음을 통해 공유

sp_executesql(Transact-SQL)

구문

인수

[ @stmt = ] N'statement'

[ @params = ] N'@parameter_name data_type [ , ...n ]'

[ @param1 = ] 'value1'

{ OUT | OUTPUT }

[ ... n ]

반환 코드 값

결과 집합

설명

OPTIMIZED_SP_EXECUTESQL

사용 권한

예제

A. SELECT 문 실행

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

C. OUTPUT 매개 변수 사용

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

D. SELECT 문 실행

관련 콘텐츠

피드백

추가 리소스