SQL Server事务提交报错，如何提交剩余操作？

这个问题我之前也碰到过，核心是SQL Server对不同类型错误的事务处理机制完全不一样：

• select 1/0属于普通运行时错误（算术除以零），SQL Server只会终止当前语句的执行，整个事务依然处于可提交状态，所以后续的commit能正常完成。
• 而select case when 1=0 then 0.0 else '' end触发的是类型不匹配的严重错误：Case表达式要求then和else分支返回的数据类型必须兼容，这里0.0是decimal数值类型，''是字符串类型，这种不兼容会直接把整个事务标记为不可提交状态（Doomed Transaction）。一旦事务进入这个状态，就只能回滚，任何commit操作都会报错。

要解决这个问题，关键是在catch块里检查当前事务的状态，根据状态决定是回滚后重启事务，还是继续执行后续操作。具体实现如下：

1. 核心函数XACT_STATE()的返回值说明：

   • 返回-1：事务已不可提交，必须先回滚整个事务
   • 返回1：事务状态正常，可以继续执行后续操作
   • 返回0：当前没有活跃事务

2. 调整后的完整代码：

BEGIN TRAN
BEGIN TRY
    SELECT CASE WHEN 1=0 THEN 0.0 ELSE '' END -- 原错误语句
END TRY
BEGIN CATCH
    -- 检查事务是否进入不可提交状态
    IF XACT_STATE() = -1
    BEGIN
        PRINT '事务已不可提交，执行回滚操作'
        ROLLBACK TRAN;
        -- 回滚后重新开启事务，用于执行后续的业务操作
        BEGIN TRAN;
    END
    -- 这里可以添加错误日志记录、告警等自定义逻辑
END CATCH

SELECT 1 -- 执行你需要的其他操作

-- 最后判断事务状态，正常则提交
IF XACT_STATE() = 1
BEGIN
    COMMIT TRAN;
    PRINT '事务提交成功'
END

如果管理员定义的查询允许调整，最好直接从根源上避免触发这个错误：把Case语句的else分支改成和then分支兼容的类型，比如：

-- 方案1：把空字符串转换为decimal类型
SELECT CASE WHEN 1=0 THEN 0.0 ELSE CAST('' AS DECIMAL(18,2)) END
-- 方案2：直接返回NULL或默认数值
SELECT CASE WHEN 1=0 THEN 0.0 ELSE NULL END

这样就不会触发严重错误，事务也能按正常流程提交。

内容的提问来源于stack exchange，提问作者Włóczykij z doliny muminków