sp_executesql (Transact-SQL)

[アーティクル]
08/11/2023

適用対象:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)Microsoft Fabric のSQL エンドポイント、Microsoft Fabric の Warehouse

何度も再利用できる Transact-SQL ステートメントまたはバッチ、または動的に構築されたバッチを実行します。 Transact-SQL ステートメントまたはバッチには、埋め込みパラメーターを含めることができます。

重要

実行時にコンパイルされた 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 ステートメントまたはバッチを含む Unicode 文字列です。 @stmtは、Unicode 定数または Unicode 変数である必要があります。 + 演算子で 2 つの文字列を連結するなどの複雑な Unicode 式は使用できません。文字定数も使用できません。 Unicode 定数を指定する場合は、プレフィックスとして N を付ける必要があります。たとえば、Unicode 定数 N'sp_who' は有効ですが、文字定数 'sp_who' は有効ではありません。文字列のサイズは、使用可能なデータベースサーバーメモリによってのみ制限されます。 64 ビットサーバーでは、文字列のサイズは nvarchar(max) の最大サイズである 2 GB に制限されます。

注意

@stmtには、変数名と同じ形式のパラメーターを含めることができます。次に例を示します。 N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'

@stmtに含まれる各パラメーターには、@params パラメーター定義リストとパラメーター値リストの両方に対応するエントリが必要です。

[ @params= ]N'@parameter_namedata_type [ ,... n ] '
@stmtに埋め込まれているすべてのパラメーターの定義を含む 1 つの文字列です。文字列は、Unicode 定数または Unicode 変数である必要があります。各パラメーター定義は、パラメーター名とデータ型で構成されます。 n は、追加のパラメーター定義を示すプレースホルダーです。 @stmtで指定されるすべてのパラメーターは、@paramsで定義する必要があります。 @stmtの Transact-SQL ステートメントまたはバッチにパラメーターが含まれていない場合、@paramsは必要ありません。このパラメーターの既定値は NULL です。

[ @param1= ]'value1'
パラメーター文字列に定義する最初のパラメーターの値を指定します。 Unicode 定数または Unicode 変数を指定できます。 @stmtに含まれるすべてのパラメーターにパラメーター値を指定する必要があります。@stmtの Transact-SQL ステートメントまたはバッチにパラメーターがない場合、値は必要ありません。

[ OUT | OUTPUT ]
パラメーターが出力パラメーターであることを示します。 プロシージャが共通言語ランタイム (CLR) プロシージャでない限り、text、ntext、および image パラメーターを OUTPUT パラメーターとして使用できます。 OUTPUT キーワードを使用する出力パラメーターは、プロシージャが CLR プロシージャでない限り、カーソルのプレースホルダーにできます。

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.EmployeeAdventureWorks2022取得し、出力パラメーターで返します@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 ステートメントの実際のテキストは実行間で変更されないため、クエリオプティマイザーは、2 回目の実行の Transact-SQL ステートメントと、最初の実行に対して生成された実行プランと一致する可能性があります。したがって、SQL Server は 2 番目のステートメントをコンパイルする必要はありません。
Transact-SQL 文字列は 1 回だけビルドされます。
整数パラメーターはネイティブ形式で指定します。 Unicode にキャストする必要はありません。

アクセス許可

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 年の月ごとに 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を使用する場合、生成される INSERT 文字列のバージョンは 12 個のみで、月次テーブルごとに 1 つだけです。 EXECUTE では、パラメーター値が異なるため、各 INSERT 文字列は一意です。どちらのメソッドも同じ数のバッチを生成しますが、sp_executesqlによって生成される INSERT 文字列の類似性により、クエリオプティマイザーが実行プランを再利用する可能性が高くなります。

.C OUTPUT パラメーターを使用する

次の例では、パラメーターを OUTPUT 使用して、ステートメントによって生成された結果セットを SELECT パラメーターに @SQLString 格納します。その後、パラメーターの値を使用する 2 つの SELECT ステートメントが OUTPUT 実行されます。

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、Analytics Platform System (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、Analytics Platform System (PDW)

D. 簡単な SELECT ステートメントを実行する

参照

その他のリソース

その他のリソース

sp_executesql (Transact-SQL)

構文

引数

リターン コードの値

結果セット

解説

アクセス許可

例

A。 簡単な SELECT ステートメントを実行する

B. 動的に構築された文字列の実行

.C OUTPUT パラメーターを使用する

例: Azure Synapse Analytics、Analytics Platform System (PDW)

D. 簡単な SELECT ステートメントを実行する

参照

その他のリソース

その他のリソース

リターンコードの値

A。簡単な SELECT ステートメントを実行する