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