如何创建使用表值参数、支持产品列表的新增订单存储过程

问题梳理 #

• 现有三张表：Products、Orders、OrderDetails，已实现新增Product的存储过程
• 需要创建新增Order的存储过程，要求：
  • 允许传入订单信息及对应产品列表
  • 下单时扣减Products表的QtyInStock字段
  • 必须使用表值参数
• 不清楚执行存储过程时如何传递键值对列表，提供的尝试代码存在语法和逻辑错误

错误代码分析 #

你尝试的代码存在以下问题：

1. 表值参数OrderInfo设计不合理：订单头信息与订单明细字段混存，会导致订单头数据重复
2. 存储过程OrderData无核心业务逻辑，仅做表值参数查询
3. 嵌套INSERT语句语法错误，不符合SQL规范
4. 未实现库存扣减逻辑
5. 未添加事务控制，数据一致性无法保障

正确实现方案 #

1. 设计合理的表值参数 #

假设Orders表的OrderID为自增主键，无需在参数中传入，表值参数只需包含订单头核心信息与明细数据：

CREATE TYPE OrderRequest AS TABLE
(
    OrderDate DATE,
    DeliveryAddress VARCHAR(100),
    ProductID INT,
    Quantity INT
)

2. 创建完整业务逻辑的存储过程 #

存储过程需完成订单头插入、明细插入、库存扣减，并通过事务保证原子性：

CREATE PROCEDURE InsertNewOrder
    @OrderTVP OrderRequest READONLY
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 1. 插入订单头并获取新增订单ID
        DECLARE @NewOrderID INT;
        INSERT INTO Orders (OrderDate, DeliveryAddress)
        SELECT DISTINCT OrderDate, DeliveryAddress FROM @OrderTVP;
        SET @NewOrderID = SCOPE_IDENTITY();

        -- 2. 插入订单明细
        INSERT INTO OrderDetails (OrderID, ProductID, Quantity)
        SELECT @NewOrderID, ProductID, Quantity FROM @OrderTVP;

        -- 3. 扣减对应产品库存（确保库存充足）
        UPDATE p
        SET p.QtyInStock = p.QtyInStock - od.TotalQty
        FROM Products p
        JOIN (
            SELECT ProductID, SUM(Quantity) AS TotalQty
            FROM @OrderTVP
            GROUP BY ProductID
        ) od ON p.ProductID = od.ProductID
        WHERE p.QtyInStock >= od.TotalQty;

        COMMIT TRANSACTION;
        SELECT @NewOrderID AS NewOrderID; -- 返回新增订单ID
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW; -- 抛出错误信息
    END CATCH
END

3. 执行存储过程时传递参数 #

在SQL Server中，需先声明表值参数变量，插入数据后再传入存储过程：

-- 声明表值参数变量
DECLARE @OrderParam OrderRequest;

-- 插入订单信息与产品列表（同一订单的头信息需保持一致）
INSERT INTO @OrderParam (OrderDate, DeliveryAddress, ProductID, Quantity)
VALUES 
    ('2024-05-20', '北京市朝阳区XX街道', 1, 2),
    ('2024-05-20', '北京市朝阳区XX街道', 3, 1);

-- 执行存储过程
EXEC InsertNewOrder @OrderParam;

如果在应用程序中传递（以C#为例），可使用DataTable构造表值参数：

DataTable orderTable = new DataTable();
orderTable.Columns.Add("OrderDate", typeof(DateTime));
orderTable.Columns.Add("DeliveryAddress", typeof(string));
orderTable.Columns.Add("ProductID", typeof(int));
orderTable.Columns.Add("Quantity", typeof(int));

// 添加订单数据
orderTable.Rows.Add(DateTime.Now, "北京市朝阳区XX街道", 1, 2);
orderTable.Rows.Add(DateTime.Now, "北京市朝阳区XX街道", 3, 1);

// 调用存储过程
using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("InsertNewOrder", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@OrderTVP", SqlDbType.Structured).Value = orderTable;
    conn.Open();
    int newOrderId = (int)cmd.ExecuteScalar();
}

关键说明 #

• 若OrderID非自增主键，需在表值参数中传入，且确保同一订单的OrderID一致
• 库存扣减时加入库存充足判断，可根据业务需求调整错误处理逻辑
• 事务控制确保所有操作要么全部成功，要么全部回滚，避免数据不一致

内容的提问来源于stack exchange，提问作者Jordy Nelson