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 ステートメントは、アプリケーションを悪意のある攻撃に晒す可能性があります。
構文
-- 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= ] 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) プロシージャでない場合は、OUTPUT パラメーターとして text、ntext、image パラメーターを使用できます。 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 ステートメント終了時まで有効です。
Transact-SQL ステートメントに対するパラメーターの値だけが変わる場合は、ストアド プロシージャの代わりに sp_executesql を使って、ステートメントを何回でも実行できます。 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 AdventureWorks2012.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 で使用することもできます。 次の例では、AdventureWorks2012.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 AdventureWorks2012.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 ステートメントが、1 回目の実行で生成された実行プランと一致するはずです。 したがって、SQL Server では 2 回目のステートメントをコンパイルする必要がありません。
Transact-SQL の文字列は 1 回だけ作成されます。
整数パラメーターはネイティブ形式で指定します。 Unicode にキャストする必要はありません。
アクセス許可
public ロールのメンバーシップが必要です。
例
A. 簡単な SELECT ステートメントを実行する
次の例では、@level という名前の埋め込みパラメーターが含まれる簡単な SELECT ステートメントを作成して実行します。
EXECUTE sp_executesql
N'SELECT * FROM AdventureWorks2012.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 文字列は各月のテーブルごとに 1 つずつ、12 とおり作成されます。 EXECUTE では、パラメーター値が異なるため、各 INSERT 文字列は一意です。 どちらの方法でも作成するバッチの数は同じですが、sp_executesql で作成される INSERT 文字列には類似性があるので、クエリ オプティマイザーで実行プランを再利用しやすくなります。
C. OUTPUT パラメーターを使用する
次の例では、OUTPUT パラメーターを使って、@SQLString パラメーターの SELECT ステートメントによって生成された結果セットを格納します。その後、OUTPUT パラメーターの値を使って 2 つの SELECT ステートメントが実行されます。
USE AdventureWorks2012;
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 AdventureWorks
EXECUTE sp_executesql
N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee
WHERE EmployeeKey = @level',
N'@level TINYINT',
@level = 109;