SQLServer表值参数如何高效传递使用？

什么是表值参数？

表值参数是一种用户定义的表类型,它允许你将一个内存中的表结构作为参数传递给存储过程或函数。

想象一下,传统方式你想向存储过程传入多行数据，你可能需要：

• 创建一个临时表或表变量,插入数据，然后在存储过程中查询它。
• 或者,将所有数据拼接成一个巨大的字符串，然后在存储过程中解析（这种方法性能差且容易出错）。

表值参数解决了这个问题，它就像是存储过程和函数的“输入表”，你可以轻松地在客户端代码（如 C#、VB.NET）中填充数据，然后一次性地传递给服务器。

为什么使用表值参数？（优点）

1. 高性能：

   • 数据作为参数流式传输到服务器,而不是像临时表那样需要额外的 I/O 操作来创建和填充。
   • 对于大数据集,性能提升非常明显。

2. 简化数据访问逻辑：

   
   （图片来源网络，侵删）
   • 客户端代码（如 C#）可以直接将 DataTable 或 List<T> 对象作为参数传递，无需手动处理复杂的字符串拼接或 XML 解析。

3. 增强安全性：

   • 表值参数是强类型的,它们遵循所有参数化的数据访问规则，可以有效防止 SQL 注入攻击，你不需要手动拼接 SQL 语句。

4. 减少往返：

   相比于多次调用存储过程来插入单行数据,使用表值参数只需一次网络往返即可完成所有数据的处理。

5. 可读性和可维护性：

   
   （图片来源网络，侵删）

   代码更清晰,存储过程的定义清楚地表明它期望接收一个表结构，这使得代码更容易理解和维护。

如何使用表值参数？（分步指南）

使用表值参数需要三个步骤：

1. 在数据库中定义表类型。
2. 创建一个接受该表类型参数的存储过程或函数。
3. 在客户端代码中填充数据并调用该存储过程。

示例场景：批量插入用户订单

假设我们有一个 Orders 表，现在需要批量插入一批新订单。

步骤 1：在 SQL Server 中定义表类型

我们需要创建一个用户定义的表类型,这个类型定义了我们将要传递的表的结构。

-- 创建一个名为 OrderListTT 的表类型
CREATE TYPE OrderListTT AS TABLE
(
    OrderID      INT,           -- 订单ID
    ProductName  NVARCHAR(100), -- 产品名称
    Quantity     INT,           -- 数量
    Price        DECIMAL(10, 2) -- 单价
);

注意：表类型中的列不能是 IDENTITY（自增）列或 TIMESTAMP 列，因为这些列的值由数据库自动生成。

步骤 2：创建使用该表类型参数的存储过程

我们创建一个存储过程,它接受一个 OrderListTT 类型的参数。

CREATE PROCEDURE dbo.usp_InsertBulkOrders
    -- 声明一个名为 @NewOrders 的参数，其类型为上面定义的 OrderListTT
    @NewOrders OrderListTT READONLY 
AS
BEGIN
    SET NOCOUNT ON;
    -- 将表值参数中的数据插入到实际的 Orders 表中
    INSERT INTO dbo.Orders (OrderID, ProductName, Quantity, Price)
    SELECT OrderID, ProductName, Quantity, Price
    FROM @NewOrders;
END
GO

关键点：

• 参数 @NewOrders 的类型是 OrderListTT。
• 必须为表值参数指定 READONLY 选项，这意味着你不能在存储过程内部修改传入的表（UPDATE @NewOrders...），但可以读取和插入到其他表中。

步骤 3：在客户端代码（C# 示例）中调用

这是最常见的使用场景,我们将使用 ADO.NET 来调用这个存储过程。

using System;
using System.Data;
using System.Data.SqlClient;
public class TableValueParameterExample
{
    public static void Main(string[] args)
    {
        // 1. 定义数据库连接字符串
        string connectionString = "Server=your_server;Database=your_db;User Id=your_user;Password=your_password;";
        // 2. 创建一个 DataTable，其结构与数据库中定义的 OrderListTT 类型匹配
        DataTable ordersTable = new DataTable();
        ordersTable.Columns.Add("OrderID", typeof(int));
        ordersTable.Columns.Add("ProductName", typeof(string));
        ordersTable.Columns.Add("Quantity", typeof(int));
        ordersTable.Columns.Add("Price", typeof(decimal));
        // 3. 向 DataTable 中添加数据（模拟从 UI 或其他来源获取的数据）
        ordersTable.Rows.Add(101, "Laptop", 1, 1200.50m);
        ordersTable.Rows.Add(102, "Mouse", 2, 25.00m);
        ordersTable.Rows.Add(103, "Keyboard", 1, 75.99m);
        // 4. 使用 using 语句确保资源被正确释放
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            // 5. 创建 SqlCommand 对象，指定存储过程名称
            using (SqlCommand command = new SqlCommand("dbo.usp_InsertBulkOrders", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                // 6. 创建 SqlParameter 并设置其类型为结构化 (Structured)
                SqlParameter tvpParam = command.Parameters.AddWithValue("@NewOrders", ordersTable);
                tvpParam.SqlDbType = System.Data.SqlDbType.Structured; // 关键！
                // 7. 设置表类型的名称（必须与数据库中定义的 TYPE 名称完全一致）
                tvpParam.TypeName = "dbo.OrderListTT";
                // 8. 执行命令
                command.ExecuteNonQuery();
                Console.WriteLine("批量订单插入成功！");
            }
        }
    }
}

C# 代码关键点：

• DataTable 的列名和数据类型必须与数据库中定义的 OrderListTT 类型严格匹配（列名不区分大小写，但数据类型必须兼容）。
• SqlParameter.SqlDbType 必须设置为 Structured。
• SqlParameter.TypeName 必须设置为数据库中定义的 TYPE 的完整名称（包括 schema，如 dbo.OrderListTT）。

限制与注意事项

1. 只读：如前所述，表值参数在存储过程内部是 READONLY 的，不能被修改。
2. 不能用于 OUTPUT：表值参数不能被声明为 OUTPUT 参数，它只用于数据输入。
3. 不能用于 DDL：不能在存储过程内部对表值参数执行 CREATE TABLE、ALTER TABLE 等 DDL 操作。
4. 行数限制：虽然表值参数可以处理大量数据，但 SQL Server 对其大小有限制（通常是每个参数最多 2GB），对于超大数据集，可能需要考虑其他方案（如批量插入）。
5. 表类型名称：TypeName 必须准确无误，否则会报错。

与临时表的对比

表值参数是 SQL Server 中处理批量数据传递的利器，当你需要将一个集合（列表、数组、表）从应用程序传递到数据库服务器进行处理时，它应该是首选方案，它不仅代码更简洁、更安全，而且在性能上通常也优于使用临时表或字符串拼接的传统方法。