如何通过SQL批量更新对应ID的销售记录取消日期及审计字段

当前手动单条更新方式 #

当销售记录的取消日期超出前端系统限制范围时，我目前使用以下单条SQL语句手动更新：

DECLARE @IDnumber VARCHAR(50) = 'XX00999999'
DECLARE @canceldate DATETIME = CAST('2022-11-01 14:15' AS DATETIME)

BEGIN TRANSACTION

UPDATE [dbo].[XSaleHeader]
SET CANCELLEDDATE = @canceldate, 
    CANCELLEDUSERID = 999, 
    CANCELLEDUSERISSYSUSER = 1
WHERE XIDNUMBER = @IDnumber

ROLLBACK TRANSACTION
--COMMIT TRANSACTION

批量处理需求 #

每月末我都会收到一批包含销售ID和取消日期的Excel数据（示例如下），希望无需修改成本高昂的前端系统，通过优化SQL实现高效批量更新：

具体要求：

• 将Excel中的每一对ID和日期传入SQL，批量为对应销售记录设置取消日期
• 统一将取消日期转换为当日09:00的时间格式
• 自动填充CANCELLEDUSERID = 999和CANCELLEDUSERISSYSUSER = 1这两个审计字段
• 避免编写大量CASE语句或循环逻辑，提升处理效率

我尝试过用Excel的TEXTJOIN生成数据列表，但还需要合适的SQL实现方案。

步骤1：创建表值参数类型 #

先定义一个自定义表类型，用于接收批量的ID和日期数据：

CREATE TYPE dbo.SaleCancelBatchType AS TABLE (
    IDNUMBERS VARCHAR(50) NOT NULL,
    CancelDateInput VARCHAR(20) NOT NULL -- 兼容Excel传入的日期字符串格式
)

步骤2：编写批量更新存储过程 #

创建存储过程，接收表值参数，处理日期转换并执行批量更新：

CREATE PROCEDURE dbo.BatchUpdateSaleCancelDate
    @BatchData dbo.SaleCancelBatchType READONLY
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION;
    BEGIN TRY
        -- 批量更新：关联表值参数与销售表头表，统一设置09:00时间
        UPDATE sh
        SET sh.CANCELLEDDATE = DATEADD(HOUR, 9, CAST(b.CancelDateInput AS DATE)),
            sh.CANCELLEDUSERID = 999,
            sh.CANCELLEDUSERISSYSUSER = 1
        FROM [dbo].[XSaleHeader] sh
        INNER JOIN @BatchData b ON sh.XIDNUMBER = b.IDNUMBERS;

        COMMIT TRANSACTION;
        PRINT '批量更新完成，共处理 ' + CAST(@@ROWCOUNT AS VARCHAR) + ' 条记录';
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        PRINT '更新失败：' + ERROR_MESSAGE();
        THROW; -- 抛出错误便于排查
    END CATCH
END

步骤3：Excel数据传入方式 #

如果是手动执行，可以通过以下方式快速导入Excel数据：

1. 在Excel中，用TEXTJOIN生成插入表值参数的语句：
   • 假设数据在A2:B3单元格，在C2单元格输入公式：

     ="('"&A2&"', '"&TEXT(B2, "yyyy-MM-dd")&"'),"
     

   • 下拉填充后，复制所有生成的行，拼接成如下SQL语句：

     DECLARE @BatchData dbo.SaleCancelBatchType;
     INSERT INTO @BatchData (IDNUMBERS, CancelDateInput)
     VALUES
         ('XX0999998', '2022-11-10'),
         ('XX0999999', '2022-11-10');
     
     EXEC dbo.BatchUpdateSaleCancelDate @BatchData;
     

2. 执行上述SQL即可完成批量更新。

方案优势 #

• 无需循环或大量CASE语句，通过JOIN实现高效批量更新，性能远优于逐条更新
• 表值参数支持批量数据传递，避免SQL注入风险
• 事务处理保证更新的原子性，要么全部成功要么全部回滚
• 日期转换逻辑统一，确保所有取消日期都设置为当日09:00

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