sp_executesql (Transact-SQL)
適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric の SQL 分析エンドポイントMicrosoft Fabric のウェアハウス
何度も再利用できる Transact-SQL ステートメントまたはバッチ、または動的に構築された Transact-SQL ステートメントまたはバッチを実行します。 Transact-SQL のステートメントやバッチには、埋め込みパラメーターを含めることができます。
注意事項
ランタイム コンパイル Transact-SQL ステートメントは、悪意のある攻撃にアプリケーションを公開する可能性があります。 を使用 sp_executesql
するときは、クエリをパラメーター化する必要があります。 詳細については、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_executesql
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 クエリ オプティマイザーは最初の実行で生成した実行プランをおそらく再使用できます。 このシナリオでは、パフォーマンスはストアド プロシージャと同等です。
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.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
実行するよりも次の利点があります。
文字列内の 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;
関連するコンテンツ
フィードバック
https://aka.ms/ContentUserFeedback」を参照してください。
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「フィードバックの送信と表示