Multiple update operations in a stored procedure, followed by an exception to the update operation, if you do not manually rollback the previously modified data is not automatically revoked!
BEGINTRYBEGIN TRAN-- ..... COMMIT TRANENDTRYBEGINCATCHROLLBACK TRAN DECLARE @ErrorMessag NVARCHAR(255) SELECT @ErrorMessag =error_message ()RAISERROR(15600,-1,-1,@ErrorMessag);ENDCatch
Another reference: http://msdn.microsoft.com/zh-cn/library/ms178592.aspx
BEGINTRY--RAISERROR with severity 11-19 would cause execution to --Jump to the CATCH block. RAISERROR('Error raised in TRY block.',--Message text. -,--Severity. 1 --State . );ENDTRYBEGINCATCHDECLARE @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 the CATCH block. RAISERROR(@ErrorMessage,--Message text. @ErrorSeverity,--Severity. @ErrorState --State . );ENDCATCH;
SQL Server exception capture, rollback, and then throw