语言

表值参数

表值参数提供了一将多行数据从客户端应用程序封送到 SQL Server 的种简单方法，而无需进行多次往返或特殊的服务器端逻辑来处理数据。 可使用表值参数来封装客户端应用程序中的数据行，并以单个参数化命令将数据发送到服务器。 传入数据行存储在随后可使用 Transact-SQL 进行操作的表变量中。

可以使用标准 Transact-SQL SELECT 语句访问表值参数中的列值。 表值参数为强类型，其结构会自动进行验证。 表值参数的大小仅受服务器内存的限制。

注释

无法返回表值参数中的数据。 表值参数仅用作输入参数；不支持关键字 OUTPUT。

若要详细了解表值参数，请参阅以下资源。

资源 DESCRIPTION
使用表值参数（数据库引擎） 介绍如何创建和使用表值参数。
用户定义的表类型 说明用于声明表值参数的用户定义的表类型。

在 SQL Server 的早期版本中传递多行

在 SQL Server 2008 中引入表值参数之前，用于将多行数据传递到存储过程或参数化 SQL 命令的选项受到限制。 开发人员可以选择下面的一种方法，将多行传递到服务器：

  • 使用一系列单独的参数来表示多列和多行数据中的值。 使用这种方法可以传递的数据量受到允许使用的参数数量限制。 SQL Server 过程最多可以有 2100 个参数。 必须使用服务器端逻辑，将这些单独的值汇编到表变量或临时表中以供处理。

  • 将多个数据值绑定到分隔字符串或 XML 文档，然后将这些文本值传递到过程或语句。 这要求过程或语句包含验证数据结构和解除绑定值所需的逻辑。

  • 针对影响多个行的数据修改创建一系列的单个 SQL 语句，例如通过 SqlDataAdapter 的 Update 方法创建的内容。 单独或批量提交更改。 即使在批处理中，服务器也会单独执行每个语句。

  • 使用 bcp 实用工具或 SqlBulkCopy 对象将多行数据加载到表中。 尽管此方法非常高效，但它不支持服务器端处理，除非将数据加载到临时表或表变量中。

创建表值参数类型

表值参数基于使用 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）

表值参数可用于基于集合的数据修改，这些修改通过执行一个语句来影响多行。 例如，可以选择表值参数中的所有行并将它们插入数据库表，也可以通过将表值参数联接到要更新的表来创建更新语句。

以下 Transact-SQL UPDATE 语句演示如何通过将表值参数联接到 Categories 表来使用表值参数。 在JOIN子句中将表值参数与FROM一起使用时，还必须为其添加别名，如下所示，其中表值参数的别名为“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 包并导入System.Data和Microsoft.Data.SqlClient。 对于客户端记录序列，请使用 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。 若要查看如何填充变量，请参阅下一节中的示例，将表值参数传递给存储过程。

// 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 语句

以下示例使用 dbo.Categories 语句和读取表值参数的 INSERT 子查询将数据插入 SELECT。 对于参数化 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 以使用单个输入参数调用存储过程。 它将SqlDbType设置为Structured，并通过OracleDataReader将AddWithValue结果集作为表值参数传递。

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

另请参阅

  • 上次更新时间为