sp_executesql (Transact-SQL)

[アーティクル]
05/09/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 のステートメントやバッチには、埋め込みパラメーターを含めることができます。

注意事項

ランタイムコンパイル Transact-SQL ステートメントは、悪意のある攻撃にアプリケーションを公開する可能性があります。を使用 sp_executesqlするときは、クエリをパラメーター化する必要があります。詳細については、SQL インジェクションを参照してください。

Transact-SQL 構文表記規則

構文

SQL Server、Azure SQL Database、Azure SQL Managed Instance、Azure Synapse Analytics、Analytics Platform System (PDW) の構文。

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 ステートメントまたはバッチを含む 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使用できます。キーワード (keyword)をOUTPUT使用する出力パラメーターは、プロシージャが CLR プロシージャでない限り、カーソルプレースホルダーにすることができます。

[ ...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 クエリオプティマイザーは最初の実行で生成した実行プランをおそらく再使用できます。このシナリオでは、パフォーマンスはストアドプロシージャと同等です。

Note

パフォーマンスを向上させるには、ステートメント文字列で完全修飾オブジェクト名を使用します。

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.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 実行するよりも次の利点があります。

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

アクセス許可

ロール public のメンバーシップが必要です。

例

A. Standard Edition LECT ステートメントを実行する

次の例では、という名前@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 ステートメントに組み込みます。

Note

これは次の基本的な例です 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 文字列は一意です。どちらのメソッドも同じ数のバッチを生成しますが、生成される文字列の INSERT 類似性により sp_executesql 、クエリオプティマイザーが実行プランを再利用する可能性が高くなります。

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. Standard Edition LECT ステートメントを実行する

次の例では、という名前@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_namedata_type [ ,... n ]'

[ @param1 = ] 'value1'

{ OUT |OUTPUT }

[ ...n ]

リターンコードの値

結果セット

解説

アクセス許可

例

A. Standard Edition LECT ステートメントを実行する

B. 動的に構築された文字列を実行する

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

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

D. Standard Edition LECT ステートメントを実行する

フィードバック

フィードバック

その他のリソース

sp_executesql (Transact-SQL)

構文

引数

[ @stmt = ] N'statement'

[ @params = ] N'@parameter_namedata_type [ ,... n ]'

[ @param1 = ] 'value1'

{ OUT |OUTPUT }

[ ...n ]

リターン コードの値

結果セット

解説

アクセス許可

例

A. Standard Edition LECT ステートメントを実行する

B. 動的に構築された文字列を実行する

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

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

D. Standard Edition LECT ステートメントを実行する

関連するコンテンツ

フィードバック

フィードバック

その他のリソース

リターンコードの値