一个存储过程中多个更新操作,后面的更新操作出现异常,如果不手动回滚前面修改的数据是不会自动撤销的!
BEGIN TRY
BEGIN TRAN
-- .....
COMMIT TRAN
END TRYBEGIN 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;