資料表值參數Table-Valued Parameters

資料表值參數提供從用戶端應用程式,將多個資料列的資料封送至 SQL Sever 的簡便方式,而不需多次來回存取或特殊的伺服器端邏輯才能處理資料。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
使用資料表值參數 (Database Engine)Use Table-Valued Parameters (Database Engine) 描述如何建立及使用資料表值參數。Describes how to create and use table-valued parameters.
使用者定義的表類型User-Defined Table Types 說明用來宣告資料表值參數的使用者定義資料表類型。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 程序最多可以有 2100 個參數。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 陳述式,例如透過呼叫 SqlDataAdapterUpdate 方法所建立的陳述式。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. 有關創建表類型的詳細資訊,請參閱使用者定義的表類型For more information about creating table types, see User-Defined Table Types.

下列陳述式會建立名為 CategoryTableType 的資料表類型,其中包含 CategoryID 與 CategoryName 資料行: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 支援從 DataTableDbDataReaderIEnumerable<T> \ SqlDataRecord物件填入資料表值參數。System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable<T> \ SqlDataRecord objects. 您必須使用 SqlParameterTypeName 屬性來指定資料表值參數的類型名稱。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 變數包含 DataTableIn 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. 程式碼會透過使用 GetChanges 方法,將加入的資料列解壓縮至新的 DataTable 之中。The code extracts added rows into a new DataTable by using the GetChanges method. 然後,程式碼會定義 SqlCommand,將 CommandType 屬性設定為 StoredProcedureThe code then defines a SqlCommand, setting the CommandType property to StoredProcedure. SqlParameter 是使用 AddWithValue 方法填入的,而且 SqlDbType 會設定為 StructuredThe SqlParameter is populated by using the AddWithValue method and the SqlDbType is set to Structured. 然後使用 ExecuteNonQuery 方法來執行 SqlCommandThe 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

下列範例示範如何使用 INSERT 陳述式搭配以資料表值參數作為資料來源的 SELECT 子查詢,將資料插入至 dbo.Category 資料表。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 陳述式時,您必須使用 SqlParameter 的新 TypeName 屬性來指定資料表值參數的類型名稱。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. 此範例中的程式碼會使用 TypeName 屬性來參考 dbo.CategoryTableType 中定義的型別結構。The code in this example uses the TypeName property to reference the type structure defined in dbo.CategoryTableType.

注意

如果您在資料表值參數中提供識別資料行的值,就必須為該工作階段發出 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. SqlParameterSqlDbType 屬性會設定為 StructuredThe SqlDbType property of the SqlParameter is set to Structured. AddWithValue 會將 OracleDataReader 結果集以資料表值參數的方式傳遞至預存程序。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