欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

SQLServer 异常捕获,回滚,再抛出

程序员文章站 2022-04-26 18:27:57
...

一个存储过程中多个更新操作, 后面的更新操作出现异常,如果 不手动回滚 前面修改的数据是不会自动撤销的! BEGIN TRY BEGIN TRAN -- ..... COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN DECLARE @ErrorMessag NVARCHAR ( 255 ) SELECT @ErrorMessag = E

一个存储过程中多个更新操作,后面的更新操作出现异常,如果不手动回滚前面修改的数据是不会自动撤销的!

BEGIN TRY
    BEGIN TRAN
-- .....
    COMMIT TRAN
END TRY

BEGIN CATCH
    ROLLBACK TRAN

    DECLARE @ErrorMessag NVARCHAR(255)
    SELECT @ErrorMessag = Error_message()
    RAISERROR (15600,-1,-1,@ErrorMessag);
END CATCH

另参考: http://msdn.microsoft.com/zh-cn/library/ms178592.aspx

BEGIN TRY
    -- RAISERROR with severity 11-19 will cause execution to 
    -- jump to the CATCH block.
    RAISERROR ('Error raised in TRY block.', -- Message text.
               16, -- Severity.
               1 -- State.
               );
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;