sp_executesql(Transact-SQL)

아티클
08/11/2023

적용 대상: Microsoft Fabric의 Microsoft Fabric Warehouse에 있는 SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Platform System(PDW) SQL 분석 엔드포인트

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

Important

시간 컴파일 Transact-SQL 문을 실행하면 애플리케이션이 악의적인 공격에 노출될 수 있습니다.

Transact-SQL 구문 표기 규칙

구문

-- Syntax for SQL Server, Azure SQL Database, Azure Synapse Analytics, Parallel Data Warehouse  
  
sp_executesql [ @stmt = ] statement  
[   
  { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }   
     { , [ @param1 = ] 'value1' [ ,...n ] }  
]

인수

[ @stmt= ] 문을
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(공용 언어 런타임) 프로시저가 아니면 text, ntext 및 이미지 매개 변수를 OUTPUT 매개 변수로 사용할 수 있습니다. 프로시저가 CLR 프로시저가 아닌 한 OUTPUT 키워드(keyword) 사용하는 출력 매개 변수는 커서 자리 표시자가 될 수 있습니다.

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

반환 코드 값

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

결과 집합

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

설명

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

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

참고 항목

이는 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

이 절차에서 sp_executesql 사용하는 것이 EXECUTE를 사용하여 문자열을 실행하는 것보다 더 효율적입니다. sp_executesql을 사용하는 경우 월별 테이블마다 하나씩 12개 버전의 INSERT 문자열만 작성됩니다. 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 문을 만들고 실행합니다.

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

참고 항목

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

sp_executesql(Transact-SQL)

구문

인수

반환 코드 값

결과 집합

설명

사용 권한

예제

A. 간단한 SELECT 문 실행

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

C. OUTPUT 매개 변수 사용

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

D. 간단한 SELECT 문 실행

참고 항목

피드백

피드백

추가 리소스