详解 SQL 中的表值参数

一、什么是 TVP?

表值参数 Table-Value Parameter (TVP) 提供一种将客户端应用程序中的多行数据封送到 SQL Server 的简单方式，而不需要多次往返或特殊服务器端逻辑来处理数据。 您可以使用表值参数来包装客户端应用程序中的数据行，并使用单个参数化命令将数据发送到服务器。 传入的数据行存储在一个表变量中，然后您可以通过使用 Transact-SQL 对该表变量进行操作。

可以使用标准的 Transact-SQL SELECT 语句来访问表值参数中的列值。  

简单点说就是当想传递 aaaa,bbbb,cccc,dddd 给存储过程时，可以先将 aaa,bbb,ccc,dddd 存到一张表中：

然后将这张表传递给存储过程。

如：当我们需要查询指定产品的信息时，通常可以传递一串产品 ID 到存储过程里面，如"1,2,3,4"，然后查询出 ID=1 或 ID=2 或 ID=3 或 ID=4 的产品信息。

可以先将"1,2,3,4"存到一张表中，然后将这张表传给存储过程。

这种方法有什么优势呢？请接着往下看。

二、TVP 传递多行

早期版本是怎么在 SQL Server 中传递多行的？ 在 SQL Server 2008 中引入表值参数之前，用于将多行数据传递到存储过程或参数化 SQL 命令的选项受到限制。 开发人员可以选择使用以下选项，将多个行传递给服务器：

使用一系列单个参数表示多个数据列和行中的值。 使用此方法传递的数据量受所允许的参数数量的限制。 SQL Server 过程最多可以有 2100 个参数。 必须使用服务器端逻辑才能将这些单个值组合到表变量或临时表中以进行处理。

将多个数据值捆绑到分隔字符串或 XML 文档中，然后将这些文本值传递给过程或语句。 此过程要求相应的过程或语句包括验证数据结构和取消捆绑值所需的逻辑。

针对影响多个行的数据修改创建一系列的单个 SQL 语句，例如通过调用 SqlDataAdapter 的 Update 方法创建的内容。 可将更改单独提交给服务器，也可以将其作为组进行批处理。 不过，即使是以包含多个语句的批处理形式提交的，每个语句在服务器上还是会单独执行。

使用 bcp 实用工具程序或 SqlBulkCopy 对象将很多行数据加载到表中。 尽管这项技术非常有效，但不支持服务器端处理，除非将数据加载到临时表或表变量中。

三、案例

当我们需要查询指定产品的信息时，通常可以传递一串产品 ID 到存储过程里面，如"1,2,3,4"，然后查询出 ID=1 或 ID=2 或 ID=3 或 ID=4 的产品信息。

我们可以先将“1,2,3,4”存到一张表中，然后作为参数传给存储过程。在存储过程里面操作这个参数。

3.1 使用 TVP 查询产品

查询产品 ID=1,2,3,4,5 的产品

public static void TestGetProductsByIDs()
{
    Collection<int> productIDs = new Collection<int>();
    Console.WriteLine();
    Console.WriteLine("----- Get Product ------");
    Console.WriteLine("Product IDs: 1,2,3,4,5");
    productIDs.Add(1);
    productIDs.Add(2);
    productIDs.Add(3);
    productIDs.Add(4);
    productIDs.Add(5);

    Collection<Product> dtProducts = GetProductsByIDs(productIDs);
    foreach (Product product in dtProducts)
    {
        Console.WriteLine("{