表值参数Table-Valued Parameters

表值参数提供一种将客户端应用程序中的多行数据封送到 SQL Server 的简单方式,不需要多次往返或特殊服务器端逻辑来处理数据。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
SQL Server 联机丛书中的表值参数 (数据库引擎)Table-Valued Parameters (Database Engine) in SQL Server Books Online 说明如何创建和使用表值参数。Describes how to create and use table-valued parameters.
SQL Server 联机丛书中的用户定义表类型User-Defined Table Types in SQL Server Books Online 说明用于声明表值参数的用户定义的表类型。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 语句,例如通过调用 UpdateSqlDataAdapter 方法创建的内容。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. 有关创建表类型的详细信息, 请参阅 SQL Server 联机丛书中的用户定义表类型For more information about creating table types, see User-Defined Table Types in SQL Server Books Online.

下面的语句可创建一个名为 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.SqlClientDataTable支持从DbDataReader或对象填充IEnumerable<T>表值参数。 \ SqlDataRecordSystem.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable<T> \ SqlDataRecord objects. 必须通过使用 TypeNameSqlParameter 属性指定表值参数的类型名称。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. 示例代码通过使用 DataTable 方法,将已添加的行提取到新的 GetChanges 中。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. 然后,通过使用 SqlCommand 方法执行 ExecuteNonQueryThe 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

下面的示例演示如何通过使用带有 SELECT 子查询(具有作为数据源的表值参数)的 INSERT 语句将数据插入 dbo.Categories 表中。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 语句时,必须通过使用 TypeName 的新 SqlParameter 属性指定表值参数的类型名称。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. SqlDbTypeSqlParameter 属性设置为 StructuredThe SqlDbType property of the SqlParameter is set to Structured. AddWithValueOracleDataReader 结果集作为表值参数传递给存储过程。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