sp_executesql (Transact-SQL)sp_executesql (Transact-SQL)

このトピックに適用されますはいSQL Server (2008 以降)はいAzure SQL DatabaseはいAzure SQL Data Warehouse [はい]。並列データ ウェアハウス THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

何回も再利用可能な、または動的に作成した Transact-SQLTransact-SQL ステートメントやバッチを実行します。Executes a Transact-SQLTransact-SQL statement or batch that can be reused many times, or one that has been built dynamically. Transact-SQLTransact-SQL ステートメントやバッチには、埋め込みパラメーターを含めることができます。The Transact-SQLTransact-SQL statement or batch can contain embedded parameters.

重要

実行時にコンパイルされる Transact-SQLTransact-SQLステートメントは、悪意のある攻撃にアプリケーションを公開できます。Run time-compiled Transact-SQLTransact-SQL statements can expose applications to malicious attacks.

トピック リンク アイコン Transact-SQL 構文表記規則Topic link icon Transact-SQL Syntax Conventions

構文Syntax

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  

sp_executesql [ @stmt = ] statement  
[   
  { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }   
     { , [ @param1 = ] 'value1' [ ,...n ] }  
]  

引数Arguments

[ @stmt= ] ステートメント[ @stmt= ] statement
Unicode 文字列が含まれて、 Transact-SQLTransact-SQLステートメントまたはバッチです。Is a Unicode string that contains a Transact-SQLTransact-SQL statement or batch. @stmt Unicode 定数または Unicode 変数のいずれかにする必要があります。@stmt must be either a Unicode constant or a Unicode variable. + 演算子で 2 つの文字列を連結するなどの複雑な Unicode 式は使用できません。More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed. 文字定数も使用できません。Character constants are not allowed. Unicode 定数が指定されている場合に付ける必要があります、 Nです。たとえば、Unicode 定数N 'sp_who'有効ですが、文字定数'sp_who'はありません。If a Unicode constant is specified, it must be prefixed with an N. For example, the Unicode constant N'sp_who' is valid, but the character constant 'sp_who' is not. 文字列のサイズは、データベース サーバーで利用可能なメモリにより制限されます。The size of the string is limited only by available database server memory. 64 ビット サーバーに、文字列のサイズは 2 GB の最大サイズに制限nvarchar (max)です。On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).

注意

@stmt たとえば、名前の変数と同じ形式を持つパラメーターを含めることができます。 N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'@stmt can contain parameters having the same form as a variable name, for example: N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'

@stmt に含める各パラメーターには、@params パラメーター定義リストとパラメーター値リストの両方に、対応するエントリが存在する必要があります。Each parameter included in @stmt must have a corresponding entry in both the @params parameter definition list and the parameter values list.

[ @params= ] N'@parameter_namedata_type [ ,... n ] '[ @params= ] N'@parameter_namedata_type [ ,... n ] '
@stmt に埋め込まれたすべてのパラメーターの定義が含まれている 1 つの文字列を指定します。この文字列は Unicode 定数または Unicode 変数にする必要があります。Is one string that contains the definitions of all parameters that have been embedded in @stmt. The string must be either a Unicode constant or a Unicode variable. 各パラメーター定義は、パラメーター名とデータ型で構成されます。Each parameter definition consists of a parameter name and a data type. n追加のパラメーター定義を示すプレース ホルダーです。n is a placeholder that indicates additional parameter definitions. すべてのパラメーターで指定された@stmtmustで定義されている@paramsです。Every parameter specified in @stmtmust be defined in @params. 場合、 Transact-SQLTransact-SQLステートメントまたはバッチに@stmtパラメーターを含まない@paramsは必要ありません。If the Transact-SQLTransact-SQL statement or batch in @stmt does not contain parameters, @params is not required. このパラメーターの既定値は NULL です。The default value for this parameter is NULL.

[ @param1=] 'value1'[ @param1= ] 'value1'
パラメーター文字列に定義する最初のパラメーターの値を指定します。Is a value for the first parameter that is defined in the parameter string. Unicode 定数または Unicode 変数を指定できます。The value can be a Unicode constant or a Unicode variable. @stmt に含まれる各パラメーターに対して、パラメーター値を指定する必要があります。値が必要なときに、 Transact-SQLTransact-SQLステートメントまたはバッチに@stmtパラメーターを持たない。There must be a parameter value supplied for every parameter included in @stmt. The values are not required when the Transact-SQLTransact-SQL statement or batch in @stmt has no parameters.

[ OUT | OUTPUT ][ OUT | OUTPUT ]
パラメーターが出力パラメーターであることを示します。Indicates that the parameter is an output parameter. テキストntext、およびイメージプロシージャが共通言語ランタイム (CLR) プロシージャでない限り、出力パラメーターとしてパラメーターを使用できます。text, ntext, and image parameters can be used as OUTPUT parameters, unless the procedure is a common language runtime (CLR) procedure. OUTPUT キーワードを使用する出力パラメーターは、プロシージャが CLR プロシージャでない限り、カーソルのプレースホルダーにできます。An output parameter that uses the OUTPUT keyword can be a cursor placeholder, unless the procedure is a CLR procedure.

nn
追加するパラメーター値のプレースホルダーです。Is a placeholder for the values of additional parameters. 定数または変数のみを指定できます。Values can only be constants or variables. 関数などの複雑な式や演算子を使用した式は指定できません。Values cannot be more complex expressions such as functions, or expressions built by using operators.

リターン コードの値Return Code Values

0 (成功) または 0 以外 (失敗)0 (success) or non-zero (failure)

結果セットResult Sets

SQL 文字列に組み込んだすべての SQL ステートメントから結果セットが返されます。Returns the result sets from all the SQL statements built into the SQL string.

解説Remarks

このトピックの「構文」セクションで説明した特定の順序で sp_executesql パラメーターを入力する必要があります。sp_executesql parameters must be entered in the specific order as described in the "Syntax" section earlier in this topic. パラメーターを不適切な順序で入力した場合、エラー メッセージが表示されます。If the parameters are entered out of order, an error message will occur.

sp_executesql は、バッチ、名前の有効範囲、およびデータベース コンテキストに関して、EXECUTE と同じように動作します。sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. Transact-SQLTransact-SQLステートメントまたはバッチに sp_executesql@stmtパラメーターは、sp_executesql ステートメントが実行されるまでコンパイルされません。The Transact-SQLTransact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. @stmt の内容は、sp_executesql を呼び出したバッチの実行プランとは別の実行プランとしてコンパイルされ、実行されます。The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. sp_executesql バッチから、sp_executesql を呼び出すバッチ内で宣言されている変数は参照できません。The sp_executesql batch cannot reference variables declared in the batch that calls sp_executesql. sp_executesql バッチ内のローカル カーソルまたはローカル変数は、sp_executesql を呼び出すバッチでは認識されません。Local cursors or variables in the sp_executesql batch are not visible to the batch that calls sp_executesql. データベース コンテキストの変更は、sp_executesql ステートメント終了時まで有効です。Changes in database context last only to the end of the sp_executesql statement.

Transact-SQLTransact-SQL ステートメントのパラメーター値だけが変わる場合は、ストアド プロシージャの代わりに sp_executesql を使用して、ステートメントを何回でも実行できます。sp_executesql can be used instead of stored procedures to execute a Transact-SQLTransact-SQL statement many times when the change in parameter values to the statement is the only variation. この場合、パラメーター値が変わるだけで Transact-SQLTransact-SQL ステートメントそのものは変わらないため、 SQL ServerSQL Server クエリ オプティマイザーではステートメントを最初に実行したときに生成した実行プランを再使用できます。Because the Transact-SQLTransact-SQL statement itself remains constant and only the parameter values change, the SQL ServerSQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

注意

パフォーマンスを向上させるには、ステートメント文字列に完全修飾オブジェクト名を使用します。To improve performance use fully qualified object names in the statement string.

sp_executesql では、次の例に示すように、 Transact-SQLTransact-SQL 文字列とは別にパラメーター値を設定できます。sp_executesql supports the setting of parameter values separately from the Transact-SQLTransact-SQL string as shown in the following example.

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 で使用することもできます。Output parameters can also be used with sp_executesql. 次の例では、AdventureWorks2012.HumanResources.Employee テーブルから役職名を取得し、それを出力パラメーター @max_title に返します。The following example retrieves a job title from the AdventureWorks2012.HumanResources.Employee table and returns it in the output parameter @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 ステートメントで文字列を実行する場合と比べて次のような利点があります。Being able to substitute parameters in sp_executesql offers the following advantages to using the EXECUTE statement to execute a string:

  • sp_executesql 文字列に指定される Transact-SQLTransact-SQL ステートメントの実際のテキストは実行のたびに変わらないので、クエリ オプティマイザーでは、2 回目の実行で Transact-SQLTransact-SQL ステートメントと最初の実行時に作成した実行プランが照合される可能性があります。Because the actual text of the Transact-SQLTransact-SQL statement in the sp_executesql string does not change between executions, the query optimizer will probably match the Transact-SQLTransact-SQL statement in the second execution with the execution plan generated for the first execution. したがって、 SQL ServerSQL Server では 2 回目のステートメントをコンパイルする必要がありません。Therefore, SQL ServerSQL Server does not have to compile the second statement.

  • Transact-SQLTransact-SQL文字列が 1 回だけを作成します。The Transact-SQLTransact-SQL string is built only one time.

  • 整数パラメーターはネイティブ形式で指定します。The integer parameter is specified in its native format. Unicode にキャストする必要はありません。Casting to Unicode is not required.

権限Permissions

public ロールのメンバーシップが必要です。Requires membership in the public role.

使用例Examples

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

次の例を作成し、実行、単純なSELECTというパラメーターを含んでいるステートメント@levelです。The following example creates and executes a simple SELECT statement that contains an embedded parameter named @level.

EXECUTE sp_executesql   
          N'SELECT * FROM AdventureWorks2012.HumanResources.Employee   
          WHERE BusinessEntityID = @level',  
          N'@level tinyint',  
          @level = 109;  

B.B. 動的に作成した文字列を実行するExecuting a dynamically built string

次の例を使用してsp_executesql動的に作成した文字列を実行します。The following example shows using sp_executesql to execute a dynamically built string. この例で使用するストアド プロシージャでは、特定の年の販売データをパーティション分割するために使用されるテーブル セットにデータを追加します。The example stored procedure is used to insert data into a set of tables that are used to partition sales data for a year. 月ごとに次の形式のテーブルが 1 つずつ存在します。There is one table for each month of the year that has the following format:

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 ステートメントを動的に作成し、実行します。This sample stored procedure dynamically builds and executes an INSERT statement to insert new orders into the correct table. この例では、受注日を使用してデータを格納するテーブルの名前を作成し、この名前を INSERT ステートメントに組み込みます。The example uses the order date to build the name of the table that should contain the data, and then incorporates that name into an INSERT statement.

注意

これは sp_executesql の簡単な使用例です。This is a simple example for sp_executesql. この例では、エラー チェックや、テーブル間における注文番号の重複の確認などのビジネス ルール チェックは行いません。The example does not contain error checking and does not include checks for business rules, such as guaranteeing that order numbers are not duplicated between tables.

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 を使用する場合と比べて効率的です。Using sp_executesql in this procedure is more efficient than using EXECUTE to execute a string. sp_executesql を使用する場合、INSERT 文字列は各月のテーブルごとに 1 つずつ、12 とおり作成されます。When sp_executesql is used, there are only 12 versions of the INSERT string that are generated, one for each monthly table. EXECUTE を使用する場合、パラメーター値が異なるので、各 INSERT 文字列は一意になります。With EXECUTE, each INSERT string is unique because the parameter values are different. どちらの方法でも作成するバッチの数は同じですが、sp_executesql で作成される INSERT 文字列には類似性があるので、クエリ オプティマイザーで実行プランを再利用しやすくなります。Although both methods generate the same number of batches, the similarity of the INSERT strings generated by sp_executesql makes it more likely that the query optimizer will reuse execution plans.

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

次の例では、OUTPUTによって生成される結果セットを格納するパラメーター、SELECT内のステートメント、@SQLStringパラメーター。2 つSELECTの値を使用するステートメントを実行し、OUTPUTパラメーター。The following example uses an OUTPUT parameter to store the result set generated by the SELECT statement in the @SQLString parameter.Two SELECT statements are then executed that use the value of the OUTPUT parameter.

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 SQL データ ウェアハウスAzure SQL Data Warehouse および Parallel Data WarehouseParallel Data WarehouseExamples: Azure SQL データ ウェアハウスAzure SQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

D.D. 簡単な SELECT ステートメントを実行するExecuting a simple SELECT statement

次の例を作成し、実行、単純なSELECTというパラメーターを含んでいるステートメント@levelです。The following example creates and executes a simple SELECT statement that contains an embedded parameter named @level.

-- Uses AdventureWorks  

EXECUTE sp_executesql   
          N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee   
          WHERE EmployeeKey = @level',  
          N'@level tinyint',  
          @level = 109;  

その他の例では、次を参照してください。 sp_executesql (TRANSACT-SQL)です。For additional examples, see sp_executesql (Transact-SQL).

参照See Also

EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
システム ストアド プロシージャ (Transact-SQL)System Stored Procedures (Transact-SQL)