sp_executesql の使用

文字列を実行するには、EXECUTE ステートメントではなく sp_executesql ストアド プロシージャを使用することをお勧めします。sp_executesql ではパラメーターの置換がサポートされるので、EXECUTE よりも汎用性が高くなります。また、sp_executesql では、SQL Server が再利用できる可能性の高い実行プランが生成されるので、EXECUTE よりも効率的です。

自己完結型バッチ

sp_executesql または EXECUTE ステートメントを使用して文字列を実行する場合、その文字列は独自の自己完結型バッチとして実行されます。SQL Server により、文字列内の Transact-SQL ステートメントがコンパイルされ実行プランが作成されますが、この実行プランは、sp_executesql または EXECUTE ステートメントを含んでいた元のバッチの実行プランとは別のものです。自己完結型バッチには、次の規則が適用されます。

• sp_executesql または EXECUTE 文字列内の Transact-SQL ステートメントは、sp_executesql または EXECUTE ステートメントが実行されるまでは実行プランにコンパイルされません。また、文字列が実行されるまでは、文字列の解析もエラーのチェックも行われません。さらに、文字列内で参照される名前も、文字列が実行されるまでは解決されません。

• 実行される文字列内の Transact-SQL ステートメントからは、sp_executesql または EXECUTE ステートメントを含むバッチ内で宣言された変数にアクセスできません。また、sp_executesql または EXECUTE ステートメントを含むバッチからは、実行される文字列内で定義された変数やローカル カーソルにアクセスできません。

• 実行される文字列内に、データベース コンテキストを変更するような USE ステートメントが含まれている場合、データベース コンテキストへの変更内容が有効であるのは sp_executesql または EXECUTE ステートメントの実行が完了するまでの間のみです。

上記の規則を適用した 2 つのバッチの実行例を次に示します。

/*Show not having access to variables from the calling batch. */
DECLARE @CharVariable CHAR(3);
SET @CharVariable = 'abc';
/* sp_executesql fails because @CharVariable has gone out of scope. */
EXECUTE sp_executesql N'PRINT @CharVariable';
GO

/* Show database context resetting after sp_executesql finishes. */
USE master;
GO
EXECUTE sp_executesql N'USE AdventureWorks2008R2;'
GO
/* This statement fails because the database context
   has now returned to master. */
SELECT * FROM Sales.Store;
GO

パラメーター値の置換

sp_executesql では、Transact-SQL 文字列内で指定したパラメーターについてパラメーター値の置換がサポートされますが、EXECUTE ステートメントではサポートされません。このため、EXECUTE ステートメントによって生成される文字列に比べると、sp_executesql によって生成される Transact-SQL 文字列の方が似通ったものになる可能性が高くなります。SQL Server クエリ オプティマイザーによる照合で、sp_executesql によって生成される Transact-SQL ステートメントと、以前に実行したステートメントから生成された実行プランが一致しやすくなり、新しい実行プランをコンパイルした場合のオーバーヘッドを節約できます。

EXECUTE ステートメントでは、すべてのパラメーター値を文字または Unicode に変換し、Transact-SQL 文字列の一部にする必要があります。

同じステートメントを繰り返し実行する場合、パラメーター値以外が同じであっても、実行のたびにまったく新しい Transact-SQL 文字列を構築する必要があります。これにより、次の点でオーバーヘッドが増加します。

• 文字列のテキスト (特に、複雑な Transact-SQL ステートメント) に含まれるパラメーター値は常に変化するので、新しい Transact-SQL 文字列と既存の実行プランを照合する SQL Server オプティマイザーの機能が妨げられます。

• 実行のたびに文字列全体を再構築する必要があります。

• 文字値または Unicode 値以外のパラメーター値は、実行のたびに文字形式または Unicode 形式にキャストする必要があります。

次のように、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 * FROM AdventureWorks2008R2.Sales.Store WHERE SalesPersonID = @SalesID';
/* Specify the parameter format one time. */
SET @ParmDefinition = N'@SalesID int';

/* Execute the string with the first parameter value. */
SET @IntVariable = 275;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 276;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;

sp_executesql には、この他に次のような利点もあります。

• Transact-SQL ステートメントの実際のテキストが実行のたびに変化しないので、クエリ オプティマイザーでは、Transact-SQL ステートメントを 2 回目に実行する際に、そのステートメントの初回実行時に生成された実行プランを照合できます。したがって、SQL Server では 2 回目のステートメントをコンパイルする必要がありません。

• Transact-SQL 文字列の構築が 1 回で済みます。

• 整数パラメーターは、ネイティブ形式で指定します。Unicode に変換する必要はありません。

  注
  SQL Server によって実行プランが再利用されるようにするには、ステートメント文字列内のオブジェクト名を完全修飾名にする必要があります。

実行プランの再利用

以前のバージョンの SQL Server では、実行プランを再利用するには、Transact-SQL ステートメントをストアド プロシージャとして定義し、そのストアド プロシージャをアプリケーションで実行するしかありませんでした。このため、アプリケーションの管理オーバーヘッドが余分に必要でした。sp_executesql を使用すると、このオーバーヘッドを軽減しながら、SQL Server による実行プランの再利用も可能になります。Transact-SQL ステートメントに指定されたパラメーター値のみを変更する場合、Transact-SQL ステートメントを複数回実行するときに sp_executesql をストアド プロシージャの代わりに使用できます。変わるのはパラメーター値のみであり、Transact-SQL ステートメント自体は変わらないので、SQL Server クエリ オプティマイザーで、ステートメントの初回実行時に生成された実行プランを再利用できる可能性が高くなります。

次の例では、DBCC CHECKDB ステートメントを構築し、サーバーにある 4 つのシステム データベース以外のすべてのデータベースについてそのステートメントを実行します。

USE master;
GO
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4
OPEN AllDatabases;

DECLARE @DBNameVar NVARCHAR(128);
DECLARE @Statement NVARCHAR(300);

FETCH NEXT FROM AllDatabases INTO @DBNameVar;
WHILE (@@FETCH_STATUS = 0)
BEGIN
   PRINT N'CHECKING DATABASE ' + @DBNameVar;
   SET @Statement = N'USE ' + @DBNameVar + CHAR(13)
      + N'DBCC CHECKDB (' + @DBNameVar + N')' + N'WITH PHYSICAL_ONLY';
   EXEC sp_executesql @Statement;
   PRINT CHAR(13) + CHAR(13);
   FETCH NEXT FROM AllDatabases INTO @DBNameVar;
END;

CLOSE AllDatabases;
DEALLOCATE AllDatabases;
GO
SET NOCOUNT OFF;
GO

実行中の Transact-SQL ステートメントに、バインド済みのパラメーター マーカーが含まれている場合、SQL Server ODBC ドライバーは sp_executesql を使用して SQLExecDirect を実装します。このため、ODBC や、ODBC に対応するように定義された API (RDO など) を使用するすべてのアプリケーションでも、sp_executesql による利点が得られます。既存の ODBC アプリケーションを SQL Server に移植した場合は、コードを書き換えなくても、自動的にパフォーマンスが向上します。ただしこれには、sp_executesql に実行時データ パラメーターを併用しないという例外があります。詳細については、「ステートメント パラメータの使用」を参照してください。

また、SQL Server Native Client ODBC プロバイダーでは、sp_executesql を使用して、パラメーターがバインドされているステートメントの直接的な実行を実装します。OLE DB または ADO を使用するアプリケーションでは、コードを書き換えなくても、sp_executesql による利点を得ることができます。

関連項目

参照

DECLARE @local_variable (Transact-SQL)

SELECT (Transact-SQL)

sp_executesql (Transact-SQL)

概念

SQL インジェクション