sp_executesql (Transact-SQL)

[アーティクル]
06/18/2013

何回も再利用可能な、または動的に作成した Transact-SQL ステートメントやバッチを実行します。 Transact-SQL ステートメントやバッチには、埋め込みパラメーターを含めることができます。

セキュリティに関する注意
実行時にコンパイルされる Transact-SQL ステートメントを使用した場合、アプリケーションが悪意のある攻撃を受ける危険性があります。

トピックリンクアイコン Transact-SQL 構文表記規則

構文

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 ]
パラメーターが出力パラメーターであることを示します。 text、ntext、および image パラメーターは、プロシージャが共通言語ランタイム (CLR) プロシージャでない限り、OUTPUT パラメーターとして使用できます。 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 ステートメントと最初の実行時に作成した実行プランが照合される可能性があります。したがって、SQL Server では 2 回目のステートメントをコンパイルする必要がありません。
Transact-SQL 文字列は一度だけ作成されます。
整数パラメーターはネイティブ形式で指定します。 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 つずつ存在します。

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 パラメーターを使用して、SELECT ステートメントによって生成された結果セットを @SQLString パラメーターに格納します。2 つの SELECT ステートメントが、OUTPUT パラメーターの値を使用して実行されます。

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;

sp_executesql (Transact-SQL)

構文

引数

リターンコードの値

結果セット

説明

権限

使用例

A. 簡単な SELECT ステートメントを実行する

B. 動的に作成した文字列を実行する

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

関連項目

参照

その他のリソース

sp_executesql (Transact-SQL)

構文

引数

リターン コードの値

結果セット

説明

権限

使用例

A. 簡単な SELECT ステートメントを実行する

B. 動的に作成した文字列を実行する

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

関連項目

参照

その他のリソース

リターンコードの値