sp_executesql (Transact-SQL)
適用対象: 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 インジェクション」を参照してください。
構文
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 クエリ オプティマイザーは最初の実行で生成した実行プランをおそらく再使用できます。 このシナリオでは、パフォーマンスはストアド プロシージャと同等です。
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
と共に使用することもできます。 次の例では、AdventureWorks2022
サンプル データベースのHumanResources.Employee
テーブルから役職を取得し、出力パラメーター @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
ステートメントに組み込みます。
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
文字列は一意です。 どちらのメソッドも同じ数のバッチを生成しますが、sp_executesql
によって生成されるINSERT
文字列の類似性により、クエリ オプティマイザーが実行プランを再利用する可能性が高くなります。
C: OUTPUT パラメーターを使用する
次の例では、 OUTPUT
パラメーターを使用して、 SELECT
ステートメントによって生成された結果セットを @SQLString
パラメーターに格納します。 その後、OUTPUT
パラメーターの値を使用する 2 つの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、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;