Table-Valued 參數

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

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

備註

您無法傳回資料表值參數中的數據。數據表值參數只是輸入;不支援 OUTPUT 關鍵詞。

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

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

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

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

使用一系列個別參數來代表多個數據行和數據列中的值。使用此方法傳遞的數據量受限於允許的參數數目。 SQL Server 程式最多可以有 2100 個參數。需要伺服器端邏輯，才能將這些個別值組合到數據表變數或臨時表進行處理。
將多個數據值組合成分隔字串或 XML 檔，然後將這些文字值傳遞至程式或語句。這需要程式或語句包含驗證數據結構及解除系結值所需的邏輯。
針對影響多行資料的修改，建立一系列個別的 SQL 語句，例如由呼叫Update方法的SqlDataAdapter 所建立的。變更可以個別提交到伺服器，或批處理成群組。不過，即使以批次方式送出包含多個語句，每個語句也會分別在伺服器上執行。
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 語句示範如何將數據表值參數聯結至 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 範例

System.Data.SqlClient支援從DataTable或DbDataReader IEnumerable<T> \ SqlDataRecord 物件填入資料表值參數。您必須使用 TypeName 屬性來指定 SqlParameter 資料表值參數的類型名稱。 TypeName必須符合先前在伺服器上建立的相容型別名稱。下列代碼段示範如何設定 SqlParameter 來插入數據。

在下列範例中，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 參數傳遞至預存程式

此範例示範如何將數據表值參數數據傳遞至預存程式。程式代碼會使用 DataTable 方法，將新增的數據列擷取到新的 GetChanges 。然後，程式代碼會 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();  
}

' 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 語句

下列範例示範如何使用具有資料表值參數作為資料來源的 SELECT 子查詢，透過 INSERT 語句將資料插入 dbo.Categories 資料表。將資料表值參數傳遞至參數化 SQL 語句時，您必須使用的新 TypeName 屬性來指定資料表值參數的類型 SqlParameter名稱。這 TypeName 必須符合先前在伺服器上建立的相容類型名稱。此範例中的程式代碼會使用 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以執行具有單一輸入參數的預存程序。 SqlDbType 的屬性 SqlParameter 被設定為 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();

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

另請參閱

Last updated on 2025-06-17

共用方式為