テーブル値パラメーター

ADO.NET のダウンロード

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

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

Note

テーブル値パラメーターでデータを返すことはできません。 テーブル値パラメーターは入力専用です。OUTPUT キーワードはサポートされていません。

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

リソース	説明
テーブル値パラメーターの使用 (データベース エンジン)	テーブル値パラメーターの作成方法および使用方法について説明します。
ユーザー定義テーブル型を作成する	テーブル値パラメーターを宣言する際に使用するユーザー定義テーブル型について説明します。
ユーザー定義テーブル型	テーブル値パラメーターを宣言する際に使用するユーザー定義テーブル型について説明します。

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

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

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

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

• 複数の行 (SqlDataAdapter の Update メソッドを呼び出すことによって作成された行など) に影響を及ぼす、データを変更するための一連の個別 SQL ステートメントを作成します。 サーバーへの変更の送信は個別に行うことも、グループにまとめることもできます。 ただし、複数のステートメントをまとめてバッチ送信しても、サーバーで実行されるときはそれぞれ個別に処理されます。

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

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

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

次のステートメントは、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)

テーブル値パラメーターを使用したデータの変更 (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;

テーブル値パラメーターの制限

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

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

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

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

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

SqlParameter 構成の例

Microsoft.Data.SqlClient では、テーブル値パラメーターのデータを DataTable、DbDataReader、または IEnumerable<T> \ SqlDataRecord オブジェクトから読み込むことができます。 SqlParameter の TypeName プロパティを使用して、テーブル値パラメーターの型名を指定します。 TypeName は、サーバーで以前に作成した互換性のある型の名前と一致する必要があります。 次のコード フラグメントは、データを挿入するために SqlParameter を構成する方法を示しています。

次の例では、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";

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;

ストアド プロシージャへのテーブル値パラメーターの受け渡し

この例は、テーブル値パラメーターのデータをストアド プロシージャに渡す方法を示しています。 追加された行が、GetChanges メソッドを使って新しい DataTable に抽出された後、 SqlCommand が定義され、CommandType プロパティが StoredProcedure に設定されます。 SqlParameter は AddWithValue メソッドを使って設定され、SqlDbType は Structured に設定されます。 次に SqlCommand メソッドを使用して 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();
}

テーブル値パラメーターをパラメーター化 SQL ステートメントに渡す

次の例は、データ ソースとしてテーブル値パラメーターを持つ SELECT サブクエリ付きの INSERT ステートメントを使用して、dbo.Categories テーブルにデータを挿入する方法を示しています。 テーブル値パラメーターをパラメーター化 SQL ステートメントに渡すときは、SqlParameter の新しい TypeName プロパティを使用して、テーブル値パラメーターの型名を指定する必要があります。 この TypeName は、サーバーで以前に作成した互換性のある型の名前と一致する必要があります。 この例のコードは、TypeName プロパティを使用して、dbo.CategoryTableType で定義されている型構造体を参照しています。

Note

テーブル値パラメーターで ID 列の値を指定する場合は、そのセッションの SET IDENTITY_INSERT ステートメントを実行する必要があります。

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

DataReader を使用した行のストリーミング

テーブル値パラメーターにデータ行をストリーム出力するには、DbDataReader から派生したオブジェクトを使用します。 次のコード フラグメントは、OracleCommand と OracleDataReader を使用して、Oracle データベースからデータを取得する方法を示しています。 その後、SqlCommand を、1 つの入力パラメーターを使用してストアド プロシージャを呼び出すように構成します。 SqlParameter の SqlDbType プロパティは Structured に設定されます。 AddWithValue は OracleDataReader の結果セットをテーブル値パラメーターとしてストアド プロシージャに渡します。

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

次のステップ

• ADO.NET での SQL Server のデータ操作