SQLserver表变量参数如何高效使用？

什么是表变量参数？

表变量参数 允许你定义一个存储过程或用户定义函数，该过程或函数可以接受一个表作为输入参数，这个参数在内部被当作一个表变量来处理，你可以在过程体或函数体中对其执行标准的 DML 操作（如 SELECT, INSERT, UPDATE, DELETE）和查询。

为什么需要使用表变量参数？（优点）

使用表变量参数相比于传统的逐行传递参数（如使用 XML 或 DataTable）或使用临时表,有以下显著优点：

1. 类型安全：参数有明确的表结构定义（列名、数据类型），编译时会进行检查,避免了运行时因数据类型不匹配导致的错误。
2. 高性能：数据作为参数传递时，只需要一次网络往返，这对于客户端需要向服务器发送大量数据的场景（例如批量插入、更新）性能提升巨大，相比之下,逐行调用存储过程会产生大量的网络开销。
3. 清晰的代码和接口：存储过程的定义非常清晰，一眼就能看出它期望接收的数据结构是什么,这使得代码更易于维护和理解。
4. 事务支持：在存储过程内部，你可以将对表变量参数的操作与过程内的其他操作放在同一个事务中,保证数据的一致性。
5. 减少锁争用：表变量参数的作用域仅限于定义它的存储过程或函数内部，不会像临时表那样产生全局对象,因此减少了与其他会话的锁争用。

如何使用表变量参数？（语法与示例）

使用表变量参数主要分为两步：

1. 定义表类型：在数据库中定义一个用户自定义的表类型,这个类型描述了参数的结构。
2. 在存储过程中使用该类型作为参数：在创建存储过程时,将这个表类型指定为参数的类型。

第 1 步：定义表类型

使用 CREATE TYPE 语句来创建一个表类型。

-- 创建一个名为 dbo.OrderDetailTableType 的表类型
CREATE TYPE dbo.OrderDetailTableType AS TABLE
(
    OrderID INT,
    ProductID INT,
    Quantity INT,
    UnitPrice DECIMAL(10, 2)
);

这个 OrderDetailTableType 就定义了一个有四列的表结构。

第 2 步：在存储过程中使用

我们可以创建一个接受这个表类型作为参数的存储过程。

-- 创建一个存储过程，用于批量插入订单详情
CREATE PROCEDURE dbo.usp_InsertOrderDetails
    -- 参数名 @OrderDetails 的类型为我们刚刚创建的表类型
    @OrderDetails dbo.OrderDetailTableType READONLY
AS
BEGIN
    SET NOCOUNT ON;
    -- 将从表变量参数中查询出的数据插入到实际的物理表中
    INSERT INTO dbo.OrderDetail (OrderID, ProductID, Quantity, UnitPrice)
    SELECT 
        OrderID, 
        ProductID, 
        Quantity, 
        UnitPrice
    FROM 
        @OrderDetails; -- @OrderDetails 就像一个普通的表变量一样使用
    -- 你也可以对它进行其他操作，比如更新、查询或计算
    DECLARE @TotalItems INT;
    SELECT @TotalItems = COUNT(*) FROM @OrderDetails;
    PRINT CONCAT('成功插入了 ', @TotalItems, ' 条订单详情记录。');
END

重要提示：

• 表变量参数在存储过程内部必须是 READONLY 的，你不能在存储过程体内对它执行 INSERT, UPDATE, DELETE 操作。
• 你只能对它执行 SELECT 查询，并将查询结果插入到其他表中（如上面的示例所示）。

如何调用带表变量参数的存储过程？

从客户端（如 C#、ADO.NET）或 T-SQL 调用时，你需要创建一个 DataTable 对象，填充数据,然后将它作为参数传递。

示例 1：从 T-SQL 调用（使用 INSERT INTO ... EXEC）

你不能直接传递一个表字面量，必须先将数据插入到一个临时表或表变量中,然后将其传递给存储过程。

-- 1. 声明一个表变量，其结构与表类型匹配
DECLARE @MyOrderDetails AS dbo.OrderDetailTableType;
-- 2. 向表变量中插入数据
INSERT INTO @MyOrderDetails (OrderID, ProductID, Quantity, UnitPrice)
VALUES 
    (101, 501, 2, 19.99),
    (101, 502, 1, 45.50),
    (102, 501, 5, 19.99);
-- 3. 调用存储过程，并将表变量作为参数传递
EXEC dbo.usp_InsertOrderDetails @OrderDetails = @MyOrderDetails;

示例 2：从 C# (ADO.NET) 调用

这是最常见的使用场景，例如在 ASP.NET 应用程序中。

using System;
using System.Data;
using System.Data.SqlClient;
public class OrderService
{
    private readonly string _connectionString;
    public OrderService(string connectionString)
    {
        _connectionString = connectionString;
    }
    public void InsertOrderDetails(DataTable orderDetailsTable)
    {
        // 验证传入的DataTable结构是否与数据库中的表类型匹配
        // ...
        using (SqlConnection connection = new SqlConnection(_connectionString))
        {
            using (SqlCommand command = new SqlCommand("dbo.usp_InsertOrderDetails", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                // 创建参数
                SqlParameter tvpParam = command.Parameters.AddWithValue("@OrderDetails", orderDetailsTable);
                // 关键：指定参数类型为结构化
                tvpParam.SqlDbType = System.Data.SqlDbType.Structured;
                // 指定对应的表类型名称
                tvpParam.TypeName = "dbo.OrderDetailTableType";
                connection.Open();
                command.ExecuteNonQuery();
            }
        }
    }
}
// --- 如何使用这个服务 ---
// 1. 创建一个DataTable，其列名和数据类型与dbo.OrderDetailTableType匹配
DataTable orderDetails = new DataTable();
orderDetails.Columns.Add("OrderID", typeof(int));
orderDetails.Columns.Add("ProductID", typeof(int));
orderDetails.Columns.Add("Quantity", typeof(int));
orderDetails.Columns.Add("UnitPrice", typeof(decimal));
// 2. 填充数据
orderDetails.Rows.Add(101, 501, 2, 19.99m);
orderDetails.Rows.Add(101, 502, 1, 45.50m);
orderDetails.Rows.Add(102, 501, 5, 19.99m);
// 3. 调用服务方法
OrderService service = new OrderString("your_connection_string");
service.InsertOrderDetails(orderDetails);

表变量参数 vs. 临时表

这是一个常见的选择题，两者各有优劣,适用于不同场景。

何时选择表变量参数？

• 首选场景：作为客户端和服务器之间的数据传输管道,将前端页面上选中的1000条记录一次性传到后端存储过程进行批量插入或更新。
• 当你只需要对传入的数据进行一次性的查询和插入操作时。

何时选择临时表？

• 当你需要在一个复杂的、多步骤的批处理过程中，多次引用和修改中间数据时。
• 当传入的数据量非常大，并且后续需要对它进行非常复杂的连接、分组操作时,手动创建索引可以大幅提升性能。
• 当你需要将数据从一个存储过程传递给另一个存储过程时。

SQL Server 表变量参数 是一个功能强大的工具，专门用于解决批量数据传输的问题，它通过提供类型安全、高性能和清晰的接口,极大地简化了应用程序与数据库之间的交互。

核心要点：

1. 先定义 TYPE，再在 PROCEDURE 中使用。
2. 参数必须是 READONLY。
3. 主要用于将客户端数据高效地传入服务器进行处理。
4. 在需要复杂处理、索引或跨过程共享数据时，临时表 仍然是更合适的选择。