テーブル値パラメーターTable-Valued Parameters

テーブル値パラメーターを使用すると、ラウンド トリップを何度も繰り返したり、サーバー側にデータを処理するための特殊なロジックを組み込んだりすることなく、複数行のデータをクライアント アプリケーションから SQL Server へと簡単にマーシャリングできます。Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. テーブル値パラメーターを使用すると、クライアント アプリケーションのデータ行をカプセル化して単一のパラメーター化コマンドでサーバーに送ることができます。You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. 受信データ行はテーブル変数に格納され、Transact-SQL によって操作できるようになります。The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

テーブル値パラメーターの列値には、Transact-SQL の標準的な SELECT ステートメントを使ってアクセスできます。Column values in table-valued parameters can be accessed using standard Transact-SQL SELECT statements. テーブル値パラメーターは厳密に型指定されており、その構造は自動的に検証されます。Table-valued parameters are strongly typed and their structure is automatically validated. テーブル値パラメーターのサイズは、サーバーのメモリによってのみ制限されます。The size of table-valued parameters is limited only by server memory.

注意

テーブル値パラメーターにデータを取得することはできません。You cannot return data in a table-valued parameter. テーブル値パラメーターは入力専用です。OUTPUT キーワードはサポートされません。Table-valued parameters are input-only; the OUTPUT keyword is not supported.

テーブル値パラメーターの詳細については、次のリソースを参照してください。For more information about table-valued parameters, see the following resources.

リソースResource 説明Description
SQL Server オンラインブックのテーブル値パラメーター (データベースエンジン)Table-Valued Parameters (Database Engine) in SQL Server Books Online テーブル値パラメーターの作成方法および使用方法について説明します。Describes how to create and use table-valued parameters.
SQL Server オンラインブックでのユーザー定義テーブル型User-Defined Table Types in SQL Server Books Online テーブル値パラメーターを宣言する際に使用するユーザー定義テーブル型について説明します。Describes user-defined table types that are used to declare table-valued parameters.

旧バージョンの SQL Server での複数行の受け渡しPassing Multiple Rows in Previous Versions of SQL Server

SQL Server 2008 にテーブル値パラメーターが導入される前に、複数行のデータをストアドプロシージャまたはパラメーター化 SQL コマンドに渡すオプションは限られていました。Before table-valued parameters were introduced to SQL Server 2008, the options for passing multiple rows of data to a stored procedure or a parameterized SQL command were limited. 複数行をサーバーに渡す方法には、次のオプションがありました。A developer could choose from the following options for passing multiple rows to the server:

  • 複数のデータ列およびデータ行の値を表す一連の個別パラメーターを使用する。Use a series of individual parameters to represent the values in multiple columns and rows of data. この方法で渡すことのできるデータの量は、使用可能なパラメーターの数によって制限されます。The amount of data that can be passed by using this method is limited by the number of parameters allowed. SQL Server プロシージャが持つことのできるパラメーター数は最大 2,100 です。SQL Server procedures can have, at most, 2100 parameters. これらの個々の値をテーブル変数または一時テーブルにまとめて処理するには、サーバー側のロジックが必要です。Server-side logic is required to assemble these individual values into a table variable or a temporary table for processing.

  • 複数のデータを区切られた文字列または XML ドキュメントとしてまとめ、そのテキスト値をプロシージャまたはステートメントに渡す。Bundle multiple data values into delimited strings or XML documents and then pass those text values to a procedure or statement. これには、データ構造を検証して値を処理するためのロジックをプロシージャまたはステートメントに含める必要があります。This requires the procedure or statement to include the logic necessary for validating the data structures and unbundling the values.

  • 複数の行に影響を与えるデータ変更のための一連の SQL ステートメントを作成する。たとえば、UpdateSqlDataAdapter メソッドを呼び出すことによって作成できます。Create a series of individual SQL statements for data modifications that affect multiple rows, such as those created by calling the Update method of a SqlDataAdapter. 変更はサーバーに個別に送ることもグループにまとめて送ることもできます。Changes can be submitted to the server individually or batched into groups. ただし、複数のステートメントを含むバッチを送信しても、サーバーでは個々のステートメントが別々に実行されます。However, even when submitted in batches that contain multiple statements, each statement is executed separately on the server.

  • bcp ユーティリティ プログラムまたは SqlBulkCopy オブジェクトを使用して、多数行のデータをテーブルに読み込む。Use the bcp utility program or the SqlBulkCopy object to load many rows of data into a table. この方法は効率的ですが、データが一時テーブルまたはテーブル変数に読み込まれなければ、サーバー側での処理がサポートされません。Although this technique is very efficient, it does not support server-side processing unless the data is loaded into a temporary table or table variable.

テーブル値パラメーター型の作成Creating Table-Valued Parameter Types

テーブル値パラメーターは、Transact-SQL の CREATE TYPE ステートメントを使用して定義された厳密に型指定されたテーブルの構造に基づいています。Table-valued parameters are based on strongly-typed table structures that are defined by using Transact-SQL CREATE TYPE statements. クライアント アプリケーションでテーブル値パラメーターを使用するには、まず SQL Server でテーブル型を作成し、その構造を定義する必要があります。You have to create a table type and define the structure in SQL Server before you can use table-valued parameters in your client applications. テーブル型の作成の詳細については、「SQL Server オンラインブックのユーザー定義テーブル型」を参照してください。For more information about creating table types, see User-Defined Table Types in SQL Server Books Online.

次のステートメントは、CategoryID と CategoryName 列から成る CategoryTableType というテーブル型を作成します。The following statement creates a table type named CategoryTableType that consists of CategoryID and CategoryName columns:

CREATE TYPE dbo.CategoryTableType AS TABLE  
    ( CategoryID int, CategoryName nvarchar(50) )  

テーブル型を作成したら、その型に基づいてテーブル値パラメーターを宣言できます。After you create a table type, you can declare table-valued parameters based on that type. 次の Transact-SQL フラグメントは、ストアド プロシージャ定義の中でテーブル値パラメーターを宣言する方法を示しています。The following Transact-SQL fragment demonstrates how to declare a table-valued parameter in a stored procedure definition. テーブル値パラメーターの宣言には READONLY キーワードが必要であることに注意してください。Note that the READONLY keyword is required for declaring a table-valued parameter.

CREATE PROCEDURE usp_UpdateCategories   
    (@tvpNewCategories dbo.CategoryTableType READONLY)  

テーブル値パラメーターによるデータの変更 (Transact-SQL)Modifying Data with Table-Valued Parameters (Transact-SQL)

テーブル値パラメーターは、単一のステートメントを実行して複数行を操作する、セット ベースのデータ変更の中で使用できます。Table-valued parameters can be used in set-based data modifications that affect multiple rows by executing a single statement. たとえば、テーブル値パラメーターのすべての行を選択し、それらをデータベース テーブルに挿入できます。また、テーブル値パラメーターを更新対象のテーブルに結合する更新ステートメントを作成することもできます。For example, you can select all the rows in a table-valued parameter and insert them into a database table, or you can create an update statement by joining a table-valued parameter to the table you want to update.

次の Transact-SQL UPDATE ステートメントは、テーブル値パラメーターを Categories テーブルに結合して使用する方法を示しています。The following Transact-SQL UPDATE statement demonstrates how to use a table-valued parameter by joining it to the Categories table. テーブル値パラメーターを FROM 句の JOIN で使用するときは、エイリアスを使用する必要があります。この例ではテーブル値パラメーターに "ec" というエイリアスが使用されています。When you use a table-valued parameter with a JOIN in a FROM clause, you must also alias it, as shown here, where the table-valued parameter is aliased as "ec":

UPDATE dbo.Categories  
    SET Categories.CategoryName = ec.CategoryName  
    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec  
    ON dbo.Categories.CategoryID = ec.CategoryID;  

この Transact-SQL の例は、単一のセット ベース操作で INSERT を実行するためにテーブル値パラメーターから行を選択する方法を示しています。This Transact-SQL example demonstrates how to select rows from a table-valued parameter to perform an INSERT in a single set-based operation.

INSERT INTO dbo.Categories (CategoryID, CategoryName)  
    SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;  

テーブル値パラメーターの制限Limitations of Table-Valued Parameters

テーブル値パラメーターにはいくつかの制限があります。There are several limitations to table-valued parameters:

  • テーブル値パラメーターをCLR ユーザー定義関数に渡すことはできません。You cannot pass table-valued parameters to CLR user-defined functions.

  • テーブル値パラメーターでは、UNIQUE 制約または PRIMARY KEY 制約をサポートするためにのみ、インデックスを付けることができます。Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints. SQL Server はテーブル値パラメーターの統計を保持しません。SQL Server does not maintain statistics on table-valued parameters.

  • テーブル値パラメーターは Transact-SQL コードの中では読み取り専用です。Table-valued parameters are read-only in Transact-SQL code. テーブル値パラメーターの行内の列の値は更新できません。行を挿入することも削除することもできません。You cannot update the column values in the rows of a table-valued parameter and you cannot insert or delete rows. テーブル値パラメーター内のストアド プロシージャやパラメーター化ステートメントに渡すデータを変更するには、一時テーブルまたはテーブル変数にデータを挿入する必要があります。To modify the data that is passed to a stored procedure or parameterized statement in table-valued parameter, you must insert the data into a temporary table or into a table variable.

  • ALTER TABLE ステートメントをテーブル値パラメーターの設計変更に使用することはできません。You cannot use ALTER TABLE statements to modify the design of table-valued parameters.

SqlParameter の構成例Configuring a SqlParameter Example

System.Data.SqlClient、、 DataTableまたはIEnumerable<T> DbDataReader オブジェクトからSqlDataRecordのテーブル値パラメーターの設定をサポートします。 \ System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable<T> \ SqlDataRecord objects. TypeNameSqlParameter プロパティを使用して、テーブル値パラメーターの型名を指定する必要があります。You must specify a type name for the table-valued parameter by using the TypeName property of a SqlParameter. TypeName は、既にサーバー上に作成されている、互換性のある型の名前と一致していることが必要です。The TypeName must match the name of a compatible type previously created on the server. 次のコード フラグメントは、データを挿入するための SqlParameter の構成方法を示しています。The following code fragment demonstrates how to configure SqlParameter to insert data.

次の例addedCategoriesでは、変数にがDataTable含まれています。In the following example, the addedCategories variable contains a DataTable. 変数がどのように設定されているかを確認するには、次のセクションの例を参照してください。テーブル値パラメーターをストアドプロシージャに渡します。To see how the variable is populated, see the examples in the next section, Passing a Table-Valued Parameter to a Stored Procedure.

// Configure the command and parameter.  
SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);  
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
tvpParam.SqlDbType = SqlDbType.Structured;  
tvpParam.TypeName = "dbo.CategoryTableType";  
' Configure the command and parameter.  
Dim insertCommand As New SqlCommand(sqlInsert, connection)  
Dim tvpParam As SqlParameter = _  
   insertCommand.Parameters.AddWithValue( _  
  "@tvpNewCategories", addedCategories)  
tvpParam.SqlDbType = SqlDbType.Structured  
tvpParam.TypeName = "dbo.CategoryTableType"  

DbDataReader から派生した任意のオブジェクトを使用して、一連の行データをテーブル値パラメーターに挿入することもできます。その方法を次のフラグメントに示します。You can also use any object derived from DbDataReader to stream rows of data to a table-valued parameter, as shown in this fragment:

// Configure the SqlCommand and table-valued parameter.  
SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);  
insertCommand.CommandType = CommandType.StoredProcedure;  
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", dataReader);  
tvpParam.SqlDbType = SqlDbType.Structured;  
' Configure the SqlCommand and table-valued parameter.  
Dim insertCommand As New SqlCommand("usp_InsertCategories", connection)  
insertCommand.CommandType = CommandType.StoredProcedure  
Dim tvpParam As SqlParameter = _  
  insertCommand.Parameters.AddWithValue("@tvpNewCategories", _  
  dataReader)  
tvpParam.SqlDbType = SqlDbType.Structured  

ストアドプロシージャへのテーブル値パラメーターの引き渡しPassing a Table-Valued Parameter to a Stored Procedure

この例は、テーブル値パラメーターのデータをストアド プロシージャに渡す方法を示しています。This example demonstrates how to pass table-valued parameter data to a stored procedure. このコードは、DataTable メソッドを使用して、追加された行を新しい GetChanges に抽出します。The code extracts added rows into a new DataTable by using the GetChanges method. 次に SqlCommand を定義し、CommandType プロパティを StoredProcedure に設定します。The code then defines a SqlCommand, setting the CommandType property to StoredProcedure. SqlParameter へのデータ入力には AddWithValue メソッドが使用され、SqlDbTypeStructured に設定されます。The SqlParameter is populated by using the AddWithValue method and the SqlDbType is set to Structured. 次に SqlCommand メソッドを使用して ExecuteNonQuery が実行されます。The SqlCommand is then executed by using the ExecuteNonQuery method.

// Assumes connection is an open SqlConnection object.  
using (connection)  
{  
  // Create a DataTable with the modified rows.  
  DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);  

  // Configure the SqlCommand and SqlParameter.  
  SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);  
  insertCommand.CommandType = CommandType.StoredProcedure;  
  SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
  tvpParam.SqlDbType = SqlDbType.Structured;  

  // Execute the command.  
  insertCommand.ExecuteNonQuery();  
}  
' Assumes connection is an open SqlConnection object.  
Using connection  
   '  Create a DataTable with the modified rows.  
   Dim addedCategories As DataTable = _  
     CategoriesDataTable.GetChanges(DataRowState.Added)  
  
  ' Configure the SqlCommand and SqlParameter.  
   Dim insertCommand As New SqlCommand( _  
     "usp_InsertCategories", connection)  
   insertCommand.CommandType = CommandType.StoredProcedure  
   Dim tvpParam As SqlParameter = _  
     insertCommand.Parameters.AddWithValue( _  
     "@tvpNewCategories", addedCategories)  
   tvpParam.SqlDbType = SqlDbType.Structured  
  
   '  Execute the command.  
   insertCommand.ExecuteNonQuery()  
End Using  

パラメーター化 SQL ステートメントへのテーブル値パラメーターの受け渡しPassing a Table-Valued Parameter to a Parameterized SQL Statement

次の例は、データ ソースとしてテーブル値パラメーターを持つ SELECT サブクエリ付きの INSERT ステートメントを使用して、dbo.Categories テーブルにデータを挿入する方法を示しています。The following example demonstrates how to insert data into the dbo.Categories table by using an INSERT statement with a SELECT subquery that has a table-valued parameter as the data source. テーブル値パラメーターをパラメーター化 SQL ステートメントに渡すときは、TypeName の新しい SqlParameter プロパティを使用して、テーブル値パラメーターの型名を指定する必要があります。When passing a table-valued parameter to a parameterized SQL statement, you must specify a type name for the table-valued parameter by using the new TypeName property of a SqlParameter. この TypeName は、既にサーバー上に作成されている、互換性のある型の名前と一致している必要があります。This TypeName must match the name of a compatible type previously created on the server. このコード例では、dbo.CategoryTableType で定義されている型の構造を参照するために TypeName プロパティが使用されています。The code in this example uses the TypeName property to reference the type structure defined in dbo.CategoryTableType.

注意

テーブル値パラメーターで ID 列の値を指定する場合は、そのセッションの SET IDENTITY_INSERT ステートメントを実行する必要があります。If you supply a value for an identity column in a table-valued parameter, you must issue the SET IDENTITY_INSERT statement for the session.

// Assumes connection is an open SqlConnection.  
using (connection)  
{  
  // Create a DataTable with the modified rows.  
  DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);  

  // Define the INSERT-SELECT statement.  
  string sqlInsert =   
      "INSERT INTO dbo.Categories (CategoryID, CategoryName)"  
      + " SELECT nc.CategoryID, nc.CategoryName"  
      + " FROM @tvpNewCategories AS nc;"  

  // Configure the command and parameter.  
  SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);  
  SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
  tvpParam.SqlDbType = SqlDbType.Structured;  
  tvpParam.TypeName = "dbo.CategoryTableType";  

  // Execute the command.  
  insertCommand.ExecuteNonQuery();  
}  
' Assumes connection is an open SqlConnection.  
Using connection  
  ' Create a DataTable with the modified rows.  
  Dim addedCategories As DataTable = _  
    CategoriesDataTable.GetChanges(DataRowState.Added)  
  
  ' Define the INSERT-SELECT statement.  
  Dim sqlInsert As String = _  
  "INSERT INTO dbo.Categories (CategoryID, CategoryName)" _  
  & " SELECT nc.CategoryID, nc.CategoryName" _  
  & " FROM @tvpNewCategories AS nc;"  
  
  ' Configure the command and parameter.  
  Dim insertCommand As New SqlCommand(sqlInsert, connection)  
  Dim tvpParam As SqlParameter = _  
     insertCommand.Parameters.AddWithValue( _  
    "@tvpNewCategories", addedCategories)  
  tvpParam.SqlDbType = SqlDbType.Structured  
  tvpParam.TypeName = "dbo.CategoryTableType"  
  
  ' Execute the query  
  insertCommand.ExecuteNonQuery()  
End Using  

DataReader による行のストリーミングStreaming Rows with a DataReader

テーブル値パラメーターにデータ行をストリーム出力するには、DbDataReader から派生したオブジェクトを使用します。You can also use any object derived from DbDataReader to stream rows of data to a table-valued parameter. 次のコード フラグメントは、OracleCommandOracleDataReader を使用して Oracle データベースからデータを取り出す方法を示しています。The following code fragment demonstrates retrieving data from an Oracle database by using an OracleCommand and an OracleDataReader. このコードは次に、単一の入力パラメーターを持つストアド プロシージャを呼び出すように SqlCommand を構成します。The code then configures a SqlCommand to invoke a stored procedure with a single input parameter. SqlDbTypeSqlParameter プロパティが Structured に設定されます。The SqlDbType property of the SqlParameter is set to Structured. AddWithValueOracleDataReader の結果セットをテーブル値パラメーターとしてストアド プロシージャに渡します。The AddWithValue passes the OracleDataReader result set to the stored procedure as a table-valued parameter.

// Assumes connection is an open SqlConnection.  
// Retrieve data from Oracle.  
OracleCommand selectCommand = new OracleCommand(  
   "Select CategoryID, CategoryName FROM Categories;",  
   oracleConnection);  
OracleDataReader oracleReader = selectCommand.ExecuteReader(  
   CommandBehavior.CloseConnection);  
  
 // Configure the SqlCommand and table-valued parameter.  
 SqlCommand insertCommand = new SqlCommand(  
   "usp_InsertCategories", connection);  
 insertCommand.CommandType = CommandType.StoredProcedure;  
 SqlParameter tvpParam =  
    insertCommand.Parameters.AddWithValue(  
    "@tvpNewCategories", oracleReader);  
 tvpParam.SqlDbType = SqlDbType.Structured;  
  
 // Execute the command.  
 insertCommand.ExecuteNonQuery();  
' Assumes connection is an open SqlConnection.  
' Retrieve data from Oracle.  
Dim selectCommand As New OracleCommand( _  
  "Select CategoryID, CategoryName FROM Categories;", _  
  oracleConnection)  
Dim oracleReader As OracleDataReader = _  
  selectCommand.ExecuteReader(CommandBehavior.CloseConnection)  
  
' Configure SqlCommand and table-valued parameter.  
Dim insertCommand As New SqlCommand("usp_InsertCategories", connection)  
insertCommand.CommandType = CommandType.StoredProcedure  
Dim tvpParam As SqlParameter = _  
  insertCommand.Parameters.AddWithValue("@tvpNewCategories", _  
  oracleReader)  
tvpParam.SqlDbType = SqlDbType.Structured  
  
' Execute the command.  
insertCommand.ExecuteNonQuery()  

関連項目See also