sp_executesql (Transact-SQL)

Applies to: 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 構文表記規則

構文

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 コード サンプルは AdventureWorks2022 または AdventureWorksDW2022 サンプル データベースを使用します。このサンプル データベースは、Microsoft SQL Server サンプルとコミュニティ プロジェクトのホーム ページからダウンロードできます。

引数

[ @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_name data_type [ , ...n ]'

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

[ @param1 = ] 'value1'

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

{ OUT |OUTPUT }

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

[ ...n ]

これは追加パラメーターの値のプレースホルダーです。 定数または変数のみを指定できます。 関数や、演算子を使用して構築された式など、より複雑な式を値にすることはできません。

リターン コードの値

0 (成功) または 0 以外 (失敗)。

結果セット

SQL 文字列に組み込まれているすべての SQL ステートメントからの結果セットを返します。

解説

sp_executesql パラメーターは、この記事で前述した「 Syntax 」セクションで説明されているように、特定の順序で入力する必要があります。 パラメーターが順に入力されていない場合は、エラー メッセージが表示されます。

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 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と共に使用することもできます。 次の例では、AdventureWorks2022 サンプル データベースのHumanResources.Employee テーブルから役職を取得し、出力パラメーター @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 ステートメントの実際のテキストは実行間で変更されないため、クエリ オプティマイザーは、2 回目の実行の Transact-SQL ステートメントと、最初の実行用に生成された実行プランと一致する可能性があります。 したがって、SQL Server は 2 番目のステートメントをコンパイルする必要はありません。

• Transact-SQL 文字列は 1 回だけビルドされます。

• 整数パラメーターはネイティブ形式で指定します。 Unicode へのキャストは必要ありません。

OPTIMIZED_SP_EXECUTESQL

適用対象: Azure SQL Database

OPTIMIZED_SP_EXECUTESQL database スコープ構成 が有効になっている場合、 sp_executesql を使用して送信されたバッチのコンパイル動作は、ストアド プロシージャやトリガーなどのオブジェクトが現在使用しているシリアル化コンパイル動作と同じになります。

バッチが同じ場合 (パラメーターの違いを除く)、 OPTIMIZED_SP_EXECUTESQL オプションは、コンパイル プロセスがシリアル化されることを保証する強制メカニズムとしてコンパイル ロックを取得しようとします。 このロックにより、複数のセッションが同時に sp_executesql 呼び出された場合、それらのセッションは、最初のセッションがコンパイル プロセスを開始した後に排他コンパイル ロックを取得しようとするときに待機します。 sp_executesqlの最初の実行では、コンパイルされたプランがコンパイルされ、プラン キャッシュに挿入されます。 他のセッションでは、コンパイル ロックの待機が中止され、プランが使用可能になったら再利用されます。

OPTIMIZED_SP_EXECUTESQL オプションを指定しない場合、sp_executesqlを介して実行された同一バッチの複数の呼び出しが並列にコンパイルされ、コンパイルされたプランの独自のコピーがプラン キャッシュに配置されます。これは、場合によってはプラン キャッシュ エントリを置き換えるか重複させます。

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 年の月ごとに 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 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 パラメーターの値を使用する 2 つの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、Analytics Platform System (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)