テーブル値パラメーター

テーブル値パラメーターを使用すると、ラウンド トリップを何度も繰り返したり、サーバー側にデータを処理するための特殊なロジックを組み込んだりすることなく、複数行のデータをクライアント アプリケーションから SQL Server へと簡単にマーシャリングできます。 テーブル値パラメーターを使用すると、クライアント アプリケーションで複数行のデータをカプセル化してサーバーに送信する処理を 1 つのパラメーター化コマンドで実行できます。 受信データ行はテーブル変数に格納され、Transact-SQL を使用して操作できます。

テーブル値パラメーター内の列の値には、標準の Transact-SQL SELECT ステートメントを使用してアクセスできます。 テーブル値パラメーターは厳密に型指定されており、その構造が自動的に検証されます。 テーブル値パラメーターのサイズは、サーバーのメモリによってのみ制限されます。

テーブル値パラメーターの詳細については、次のリソースを参照してください。

以前のバージョンの SQL Server で複数の行を渡す

SQL Server 2008 にテーブル値パラメーターが導入されるまでは、複数行データをストアド プロシージャまたはパラメーター化 SQL コマンドに渡す方法は限られていました。 開発者が選択できる複数の行をサーバーに渡すためのオプションを次に示します。

• 複数のデータ列とデータ行の値を表すには、一連の個別のパラメーターを使用します。 このメソッドを使用して渡すことができるデータの量は、許可されているパラメーター数によって制限されます。 SQL Server プロシージャが持つことのできるパラメーター数は最大 2,100 です。 これらの個々の値をテーブル変数または一時テーブルにまとめて処理するには、サーバー側ロジックが必要です。

• 複数のデータ値を区切り文字列または XML ドキュメントにバンドルし、それらのテキスト値をプロシージャまたはステートメントに渡します。 これを行うには、そのプロシージャまたはステートメントに、データ構造の検証と値のバンドルの解除に必要なロジックが含まれている必要があります。

• Update の SqlDataAdapter メソッドによって作成される、複数の行に影響するデータ変更用の一連の個々の SQL ステートメントを作成します。 変更を個別に、またはまとめて送信してください。 バッチ内であっても、サーバーは各ステートメントを個別に実行します。

• bcp ユーティリティ プログラムまたは SqlBulkCopy オブジェクトを使用して、多数のデータ行をテーブルに読み込みます。 この手法は非常に効率的ですが、データが一時テーブルまたはテーブル変数に読み込まれていない限り、サーバー側の処理はサポートされません。

テーブル値パラメーター型の作成

テーブル値パラメーターは、Transact-SQL の CREATE TYPE ステートメントを使用して定義された厳密に型指定されたテーブルの構造に基づいています。 クライアント アプリケーションでテーブル値パラメーターを使用するには、まず SQL Server でテーブル型を作成し、その構造を定義する必要があります。 テーブル型の作成の詳細については、「テーブル型 のUser-Defined」を参照してください。

次のステートメントは、CategoryID と CategoryName 列から成る CategoryTableType というテーブル型を作成します。

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

テーブル型を作成したら、その型に基づいてテーブル値パラメーターを宣言できます。 次の Transact-SQL フラグメントは、ストアド プロシージャ定義の中でテーブル値パラメーターを宣言する方法を示しています。 テーブル値パラメーターを宣言するために、 READONLY キーワードが必要であることに注意してください。

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Table-Valued パラメーターを使用したデータの変更 (Transact-SQL)

1 つのステートメントを実行することで、テーブル値パラメーターを、複数の行に影響を与えるセットベースのデータ変更で使用できます。 たとえば、テーブル値パラメーター内のすべての行を選択して、データベース テーブルに挿入できます。また、テーブル値パラメーターを更新対象テーブルに結合して、更新ステートメントを作成することもできます。

次の Transact-SQL UPDATE ステートメントは、Categories テーブルに結合してテーブル値パラメーターを使用する方法を示しています。 FROM 句の中でテーブル値パラメーターを JOIN と共に使用する場合は、次に示すように、別名も使用する必要があります。ここでは、テーブル値パラメーターは "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 を実行する方法を示します。

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

Table-Valued パラメーターの制限事項

テーブル値パラメーターにはいくつかの制限があります。

• CLR ユーザー定義関数にテーブル値パラメーターを渡すことはできません。

• テーブル値パラメーターは、 UNIQUE 制約または PRIMARY KEY 制約をサポートするためにのみインデックスを作成できます。 SQL Server では、テーブル値パラメーターの統計は保持されません。

• テーブル値パラメーターは Transact-SQL コードの中では読み取り専用です。 テーブル値パラメーターの行の列値を更新することはできません。また、行を挿入または削除することもできません。 テーブル値パラメーターのストアド プロシージャまたはパラメーター化されたステートメントに渡すデータを変更するには、そのデータを一時テーブルまたはテーブル変数に挿入する必要があります。

• ALTER TABLE ステートメントを使用して、テーブル値パラメーターの設計を変更することはできません。

SqlParameter の構成の例

Microsoft.Data.SqlClient NuGet パッケージをインストールし、Microsoft.Data.SqlClientとSystem.Dataをインポートします。 クライアント側のレコード シーケンスの場合は、 Microsoft.Data.SqlClient.Server.SqlDataRecord と Microsoft.Data.SqlClient.Server.SqlMetaDataを使用します。 Microsoft.SqlServer.Server の SQL CLR サーバー側の型を置き換えないでください。

Microsoft.Data.SqlClientでは、IEnumerable<T> オブジェクトの SqlDataRecord、DataTable、または DbDataReader からのテーブル値パラメーター値がサポートされます。 パラメーターの TypeName を、サーバーで以前に作成した互換性のあるテーブル型の名前に設定します。 次のフラグメントは、データを挿入するパラメーターを設定します。

次の例では、addedCategories 変数に DataTable が含まれています。 変数がどのように設定されているかを確認するには、次の「ストアド プロシージャへのテーブル値パラメーターの受け渡し」を参照してください。

// 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 から派生した任意のオブジェクトを使用して、一連の行データをテーブル値パラメーターに挿入することもできます。その方法を次のフラグメントに示します。

// 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

ストアド プロシージャに Table-Valued パラメーターを渡す

この例では、テーブル値パラメーター データをストアド プロシージャに渡します。 コードは GetChanges を呼び出して、新しい DataTableに追加された行を抽出します。 StoredProcedure を SqlCommand に設定した CommandType が作成されます。 パラメーターを AddWithValue と共に追加し、 SqlDbType を Structuredに設定します。 最後に、コードは ExecuteNonQueryを呼び出します。

// 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 ステートメントに Table-Valued パラメーターを渡す

次の例では、SELECT ステートメントとテーブル値パラメーターを読み取る dbo.Categories サブクエリを使用して、INSERT にデータを挿入します。 パラメーター化された SQL ステートメントの場合は、TypeName をサーバーで以前に作成した互換性のあるテーブル型の名前に設定します。 この例では、 dbo.CategoryTableTypeを使用します。

// 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

データリーダーを使用したデータ行のストリーミング

DbDataReaderから派生した任意のオブジェクトを使用して、テーブル値パラメーターに行を順次渡すことができます。 次のフラグメントは、 OracleCommand と OracleDataReaderを使用して Oracle データベースからデータを取得します。 このコードは、1 つの入力パラメーターを使用してストアド プロシージャを呼び出すように SqlCommand を構成します。 SqlDbType を Structured に設定し、OracleDataReader の結果セットを AddWithValue を介してテーブル値パラメーターとして渡します。

// 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()

こちらも参照ください

• パラメーターとパラメーター データ型の構成
• コマンドおよびパラメーター
• データアダプター パラメーター
• ADO.NET での SQL Server データ操作
• ADO.NET の概要