存储过程spNewSale多表插入行数异常，无需拆分如何实现预期逻辑

问题根因 #

你当前的存储过程单次仅支持单条商品明细处理，推测你是在前端循环遍历DataGridView行多次调用该存储过程，才会导致orders、payments表重复插入和库存重复扣减的问题。无需拆分存储过程，通过表值参数(TVP) 批量传递所有商品明细即可实现需求：

第一步：创建自定义表值类型 #

先在数据库中执行以下语句，创建用于传递订单明细的自定义表类型：

CREATE TYPE OrderLineType AS TABLE
(
    product_id INT,
    quantity INT
);

第二步：修改存储过程逻辑 #

修改后的存储过程仅会执行1次订单插入、1次支付记录插入，批量处理明细插入和库存扣减，同时增加事务保证操作原子性，避免中间出错产生脏数据：

CREATE PROCEDURE spNewSale
    @customer INT, -- 从下拉框传入的客户ID
    @staff VARCHAR(12), -- 当前登录的用户名
    @OrderLines OrderLineType READONLY -- 传入整个DataGridView的所有商品明细
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    BEGIN TRY
        -- 1. 仅插入1次订单主记录
        INSERT INTO orders (order_cust_id, order_staff_id, order_date)
        VALUES (@customer, 
                (SELECT logon_st_id FROM logons WHERE logon_name = @staff), 
                CURRENT_TIMESTAMP);
        DECLARE @ordernbr INT = SCOPE_IDENTITY();

        -- 2. 批量插入所有订单明细
        INSERT INTO orderlines (ol_order_id, ol_pro_id, ol_qty, ol_value)
        SELECT 
            @ordernbr, 
            ol.product_id, 
            ol.quantity, 
            ol.quantity * p.pro_price
        FROM @OrderLines ol
        INNER JOIN products p ON ol.product_id = p.pro_id;

        -- 3. 批量扣减对应商品库存
        UPDATE p
        SET p.pro_stock = p.pro_stock - ol.quantity
        FROM products p
        INNER JOIN @OrderLines ol ON p.pro_id = ol.product_id;

        -- 4. 仅插入1次支付记录
        INSERT INTO payments (paym_order_id, paym_amount, paym_taken_by, paym_date)
        VALUES (
            @ordernbr,
            (SELECT SUM(ol_value) FROM orderlines WHERE ol_order_id = @ordernbr),
            (SELECT logon_st_id FROM logons WHERE logon_name = @staff),
            CURRENT_TIMESTAMP
        );

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        -- 抛出错误方便前端排查问题
        THROW;
    END CATCH
END

第三步：前端调用适配（C#示例） #

调用时无需循环遍历DataGridView，直接将所有行转为DataTable作为参数传入即可：

// 构造和表值类型结构一致的DataTable
DataTable orderLinesDt = new DataTable();
orderLinesDt.Columns.Add("product_id", typeof(int));
orderLinesDt.Columns.Add("quantity", typeof(int));

// 遍历DataGridView填充明细数据
foreach (DataGridViewRow row in dataGridView1.Rows)
{
    if (!row.IsNewRow)
    {
        orderLinesDt.Rows.Add(
            Convert.ToInt32(row.Cells["你的产品ID列名"].Value),
            Convert.ToInt32(row.Cells["你的数量列名"].Value)
        );
    }
}

// 调用存储过程
using (SqlConnection conn = new SqlConnection("你的数据库连接字符串"))
{
    conn.Open();
    using (SqlCommand cmd = new SqlCommand("spNewSale", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@customer", 选中的客户ID);
        cmd.Parameters.AddWithValue("@staff", 当前登录用户名);
        // 添加表值参数
        SqlParameter tvpParam = cmd.Parameters.AddWithValue("@OrderLines", orderLinesDt);
        tvpParam.SqlDbType = SqlDbType.Structured;
        tvpParam.TypeName = "OrderLineType";
        
        cmd.ExecuteNonQuery();
    }
}

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