存储过程执行时BEGIN与COMMIT语句不匹配报错排查求助

错误原因 #

你遇到的执行后的事务计数表明BEGIN和COMMIT语句数量不匹配错误，核心是事务处理逻辑存在漏洞，导致部分场景下事务未被正确提交或回滚，同时代码还存在其他语法和逻辑问题：

• 事务处理逻辑缺陷：TRY块中仅当@ErrorCode=0时才执行COMMIT，但如果INSERT/UPDATE执行出错，@ErrorCode会不为0，此时TRY块不会提交事务；而CATCH块中用@@ERROR获取的是CATCH块内的错误号（而非原操作的错误号），导致ROLLBACK条件@ErrorCode>0可能不触发，事务一直处于未完成状态。
• 错误信息赋值时机错误：TRY块中无错误时ERROR_MESSAGE()返回NULL，不应在这里给@ErrorMessage赋值。
• 参数拼写错误：@ResolutinoDateTime拼写错误（应为@ResolutionDateTime），会导致字段赋值失败。
• UPDATE语句逻辑无效：ConfirmerId = ConfirmerId和DepartmentId = DepartmentId没有使用传入的@ConfirmedBy和@DepartmentId参数，等于未更新这两个字段。
• 默认值语法错误：@CreationDateTime DATETIME = GETDATE缺少括号，应改为GETDATE()。
• 回滚逻辑不严谨：CATCH块中未判断事务状态，直接ROLLBACK可能引发额外错误。

修正后的存储过程代码 #

ALTER PROCEDURE [dbo].[UpsertServiceTicket]
    @TransactionType INT,
    @Id INT = NULL,
    @CreationDateTime DATETIME = GETDATE(), -- 修正GETDATE语法
    @Issue VARCHAR(MAX),
    @ReportedDateTime DATETIME = NULL,
    @ResolutionDateTime DATETIME = NULL, -- 修正参数拼写
    @CreatedBy INT = NULL,
    @ServiceRequestNumber NVARCHAR(MAX),
    @Status VARCHAR(MAX) = NULL,
    @IsDeleted BIT = 0,
    @LocationId INT,
    @SubLocationId INT,
    @RequestorId INT,
    @ConfirmedBy INT = NULL,
    @DepartmentId INT,
    @ErrorMessage NVARCHAR(1000) OUTPUT,
    @ErrorCode SMALLINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET @ErrorMessage = '';
    SET @ErrorCode = 0;

    BEGIN TRY
        BEGIN TRANSACTION;

        IF (@TransactionType = 0)
            INSERT INTO ServiceTickets (
                CreationDateTime
                , Issue 
                , ReportedDateTime
                , ResolutionDateTime -- 修正字段拼写
                , CreatedBy
                , ServiceRequestNumber
                , [Status]
                , IsDeleted
                , LocationId
                , SubLocationId
                , RequestorId
                , ConfirmerId
                , DepartmentId
                )
            VALUES (
                @CreationDateTime
                , @Issue
                , @ReportedDateTime
                , @ResolutionDateTime -- 修正参数拼写
                , @CreatedBy
                , @ServiceRequestNumber
                , @Status
                , @IsDeleted
                , @LocationId
                , @SubLocationId
                , @RequestorId
                , @ConfirmedBy
                , @DepartmentId
            )
        -- 更新服务工单表
        ELSE
            UPDATE      ServiceTickets
            SET         CreationDateTime        = @CreationDateTime
                        , Issue                 = @Issue
                        , ReportedDateTime      = @ReportedDateTime
                        , ResolutionDateTime    = @ResolutionDateTime -- 修正参数拼写
                        , CreatedBy             = @CreatedBy
                        , ServiceRequestNumber  = @ServiceRequestNumber
                        , [Status]              = @Status
                        , IsDeleted             = @IsDeleted
                        , LocationId            = @LocationId
                        , SubLocationId         = @SubLocationId
                        , RequestorId           = @RequestorId
                        , ConfirmerId           = @ConfirmedBy -- 修正为传入参数
                        , DepartmentId          = @DepartmentId -- 修正为传入参数
            WHERE Id = @Id

        -- 无错误则提交事务
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- 获取原错误信息和错误码
        SET @ErrorMessage = ERROR_MESSAGE();
        SET @ErrorCode = ERROR_NUMBER();
  
        -- 判断事务状态，仅当事务可回滚时执行回滚
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;
    END CATCH
END

关键修复点说明 #

• 事务逻辑优化：TRY块中执行完操作后直接COMMIT，一旦操作出错会进入CATCH块统一处理回滚，避免事务遗漏。
• 错误信息处理：仅在CATCH块中赋值错误信息和错误码，确保获取到的是原操作的错误内容。
• 修正拼写和语法错误：修复参数和字段拼写错误、GETDATE的语法问题。
• UPDATE逻辑修正：将无效的字段赋值改为使用传入的参数。
• 严谨的回滚判断：使用XACT_STATE()判断事务状态，避免在无活跃事务时执行ROLLBACK引发错误。

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