Table-Valued 參數

數據表值參數可讓您輕鬆地將用戶端應用程式的多個數據列封送處理至 SQL Server，而不需要多次往返或特殊的伺服器端邏輯來處理數據。您可以使用資料表值參數在用戶端應用程式中封裝數據列，並以單一參數化命令將數據傳送至伺服器。傳入的數據行會儲存在數據表變數中，然後可以使用 Transact-SQL進行操作。

表格值參數中的欄位值可透過標準 Transact-SQL SELECT 語句存取。資料表值參數是強型別，其結構會被自動驗證。數據表值參數的大小只受限於伺服器記憶體。

備註

您無法傳回資料表值參數中的數據。表格值參數僅用於輸入; OUTPUT 該關鍵字不被支援。

如需數據表值參數的詳細資訊，請參閱下列資源。

資源	說明
使用資料表值參數 (資料庫引擎)	描述如何建立和使用數據表值參數。
User-Defined 數據表類型	描述用來宣告數據表值參數的使用者定義數據表類型。

在舊版 SQL Server 中傳遞多個數據列

在 SQL Server 2008 導入資料表值參數之前，將多個數據列傳遞至預存程式或參數化 SQL 命令的選項受到限制。開發人員可以選擇下列選項，將多個數據列傳遞至伺服器：

使用一系列個別參數來代表多個數據行和數據列中的值。使用此方法傳遞的數據量受限於允許的參數數目。 SQL Server 程式最多可以有 2100 個參數。需要伺服器端邏輯，才能將這些個別值組合到數據表變數或臨時表進行處理。
將多個數據值組合成分隔字串或 XML 檔，然後將這些文字值傳遞至程式或語句。這需要程式或語句包含驗證數據結構及解除系結值所需的邏輯。
建立一系列針對影響多列的資料修改所用的個別 SQL 陳述式，例如由 SqlDataAdapter 的 Update 方法所建立的。請單獨或分批提交變更。即使在批次中，伺服器也會分別執行每個語句。
bcp使用公用程式程式或 SqlBulkCopy 物件，將許多數據列載入資料表中。雖然這項技術非常有效率，但除非數據載入臨時表或數據表變數，否則不支援伺服器端處理。

建立 Table-Valued 參數類型

資料表值參數是以使用 Transact-SQL CREATE TYPE 陳述式所定義的強型別資料表結構為基礎。您必須先建立數據表類型，並在 SQL Server 中定義結構，才能在用戶端應用程式中使用數據表值參數。如需建立數據表類型的詳細資訊，請參閱 User-Defined 數據表類型。

下列語句會建立名為 CategoryTableType 的數據表類型，其中包含 CategoryID 和 CategoryName 數據行：

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）

數據表值參數可藉由執行單一語句，在影響多個數據列的集合型數據修改中使用。例如，您可以選取資料表值參數中的所有數據列，並將其插入資料庫數據表中，或者您可以將數據表值參數聯結至您要更新的數據表來建立 update 語句。

以下 Transact-SQL UPDATE 語句示範如何將一個以表值為值的參數與類別表結合。當你在 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 支援來自 SqlDataRecord 物件的 DataTable、DbDataReader 或 IEnumerable<T> 的資料表值參數值。將 TypeName 參數設定為先前在伺服器上建立的相容資料表類型名稱。以下片段設定參數以插入資料。

在下列範例中，addedCategories 變數包含 DataTable。若要查看變數的填入方式，請參閱下一節中的範例：將 Table-Valued 參數傳遞至預存程式。

// 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. 它會建立 SqlCommand ，且 CommandType 設為 StoredProcedure。它使用 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

將 Table-Valued 參數傳遞至參數化 SQL 語句

以下範例使用 INSERT 陳述式和 SELECT 子查詢，將資料插入 dbo.Categories，其中子查詢會讀取資料表值參數。對於參數化的 SQL 陳述式，則設定 TypeName 為先前在伺服器上建立的相容資料表類型名稱。這個範例使用 dbo.CategoryTableType。

備註

如果你在資料表值參數中為識別欄位指定值，就必須對該工作階段發出 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();
}

' 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 串流數據列

你可以使用任何衍生自 DbDataReader 的物件來將資料列串流到資料表值參數。以下片段使用 OracleCommand 和 OracleDataReader 從 Oracle 資料庫取得資料。程式碼會設定 SqlCommand 來呼叫具有一個輸入參數的預存程序。它將AddWithValue設為SqlDbType，並將Structured結果集透過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();

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

另請參閱

上次更新時間： 2026-10-27