SQL Server存储过程Catch块未执行：Schema删除状态异常求助

我之前处理过几乎一模一样的问题！你的核心痛点在于SQL Server里子存储过程的错误默认不会自动冒泡到外层的TRY/CATCH块，再加上没有事务控制，就会出现状态卡在DELETING却抓不到错误的情况。下面给你一步步的修复方案：

1. 先给子存储过程补全错误抛出逻辑 #

如果你的spDeleteSchema、spDeleteSchemaOwner、spDeleteSchemaUserRoles这几个子存储过程里有自己的TRY/CATCH块，但没有重新抛出错误，外层的CATCH根本感知不到。你需要给每个子过程加上错误抛出：

ALTER PROCEDURE [dbo].[spDeleteSchema] (@schemaName NVARCHAR(100))
AS
SET XACT_ABORT ON; -- 出错时立即终止并回滚
BEGIN TRY
    -- 保留你原有的删除Schema对象的逻辑
END TRY
BEGIN CATCH
    -- 把错误原样抛给外层调用方
    THROW;
END CATCH

对另外两个子过程做同样的修改。如果子过程原本没有TRY/CATCH，那至少要加上SET XACT_ABORT ON，确保严重错误时不会静默失败。

2. 重构外层存储过程，加上事务和强错误检查 #

原来的外层过程没有事务，一旦子过程出错，之前的Status=2更新已经提交，没法回滚。而且即使子过程出错，也可能因为错误级别问题触发不了外层CATCH。修改后的代码如下：

ALTER PROCEDURE [dbo].[spDeleteSchemaTotally] (@schemaName NVARCHAR(100))
AS
SET FMTONLY OFF;
SET XACT_ABORT ON; -- 严重错误时自动终止并回滚事务
BEGIN TRANSACTION; -- 开启事务，保证所有操作原子性
BEGIN TRY
    -- 标记为删除中
    UPDATE SchemaList SET [Status] = 2 /* DELETING */ WHERE SchemaName = @schemaName;

    -- 调用子过程，每一步都强制检查错误
    EXEC [dbo].[spDeleteSchema] @schemaName;
    IF @@ERROR <> 0 THROW 50001, 'Failed to delete schema objects', 1;

    EXEC [dbo].[spDeleteSchemaOwner] @schemaName;
    IF @@ERROR <> 0 THROW 50002, 'Failed to delete schema owner', 1;

    EXEC [dbo].[spDeleteSchemaUserRoles] @schemaName;
    IF @@ERROR <> 0 THROW 50003, 'Failed to delete schema user roles', 1;

    -- 标记为已删除
    UPDATE SchemaList SET [Status] = 1 /* DELETED */, 
        SchemaName = SchemaName + '|deleted|' + FORMAT(GETUTCDATE(), 'MM-d-yyyy-hh:mm') 
    WHERE SchemaName = @schemaName;

    COMMIT TRANSACTION; -- 所有操作成功，提交事务
    SELECT CAST(1 AS BIT);
END TRY
BEGIN CATCH
    -- 出错时先回滚所有操作
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    -- 标记为错误状态
    UPDATE SchemaList SET [Status] = 3 /* ERRORED */ WHERE SchemaName = @schemaName;
    -- 可选：抛出错误让后端拿到具体信息，方便排查
    THROW;
    SELECT CAST(0 AS BIT);
END CATCH

关键改进点：

• 事务控制：所有操作（状态更新、子过程调用）要么全部成功，要么全部回滚，不会出现状态卡在DELETING的情况。
• SET XACT_ABORT ON：遇到比如对象不存在、违反约束这类严重错误时，直接终止执行并回滚，避免静默失败。
• @@ERROR检查：双重保险，确保子过程的任何错误都能触发外层CATCH。
• 明确的错误抛出：CATCH里的THROW可以让后端捕获到具体的错误信息，方便你排查到底是哪一步出了问题。

3. 后端代码优化（可选但推荐） #

如果想更精准地排查错误，可以在C#代码里捕获SQL异常，记录详细错误信息：

try
{
    var result = await session.Sp<bool>(c => c.spDeleteSchemaTotally, new { schemaName})
        .SingleAsync()
        .ConfigureAwait(false);
    if (result)
    {
        tracer.Info($"Schema {schemaName} deleted successfully");
    }
    else
    {
        tracer.Warn($"Schema {schemaName} was not deleted appropriately");
    }
}
catch (SqlException ex)
{
    tracer.Error($"Failed to delete schema {schemaName}: {ex.Message}", ex);
    // 这里可以加额外处理，比如给管理员发告警等
}

为什么原来的代码没生效？ #

• 子过程如果有自己的TRY/CATCH但没有重新抛出错误，外层的TRY/CATCH根本感知不到错误，会继续执行后续步骤，或者错误级别不够触发不了外层CATCH。
• 没有事务，子过程出错时，之前的状态更新已经提交，导致状态卡在DELETING，没法回滚。

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