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

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