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

適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database はいAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance はいAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics はいParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel Data Warehouse適用対象:Applies to: はいSQL ServerSQL Server (サポートされているすべてのバージョン) yesSQL ServerSQL Server (all supported versions) はいAzure SQL データベースAzure SQL DatabaseYesAzure SQL データベースAzure SQL Database はいAzure SQL Managed InstanceAzure SQL Managed InstanceYesAzure SQL Managed InstanceAzure SQL Managed Instance はいAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics はいParallel Data WarehouseParallel Data WarehouseyesParallel Data WarehouseParallel 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 Synapse Analytics, 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ビットサーバーでは、文字列のサイズは、最大サイズである nvarchar (max) の 2 GB に制限されています。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_name data_type [,... n ] '[ @params= ] N'@parameter_name data_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. Stmt に指定するすべてのパラメーター @ は、params で定義する必要があり @ ます。Every parameter specified in @stmt must be defined in @params. Transact-SQLTransact-SQLStmt のステートメントまたはバッチにパラメーターが含まれていない場合 @ 、 @ 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. textntext、および image パラメーターは、プロシージャが共通言語ランタイム (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-SQLSp_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 では、パラメーター値が異なるため、各挿入文字列は一意です。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 ます。 SELECT 次に、パラメーターの値を使用する2つのステートメントが実行され 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 Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (SQL Data Warehouse)Parallel Data WarehouseParallel Data WarehouseExamples: Azure Synapse Analytics (SQL Data Warehouse)Azure Synapse Analytics (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;  

参照See Also

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