Most effective way to write SQL Statement to Catch and Reverse errors during Query execution – MSDN TSQL forum
I am wondering what is the most effective way to deal with errors, specifically in a Stored Procedure.
I wrote something like this:
BEGIN TRY BEGIN TRANSACTION /*My statements goes in here*/ IF ERROR_NUMBER() = 0 -- Do I need this line? COMMIT TRANSACTION; END TRY BEGIN CATCH IF ERROR_NUMBER() > 0 --Do I need this line? ROLLBACK TRANSACTION; END CATCH;
It would make sense using the if Statement when attempting to log errors.
–> My Answer:
Checking ERROR_NUMBER() within TRY BEGIN-COMMIT TRANS block is not a good idea, because as soon as an error is reported the control will be immediately redirected to the CATCH block.
So, use it like this:
BEGIN TRY BEGIN TRANSACTION /*My statements goes in here*/ COMMIT TRANSACTION; END TRY BEGIN CATCH ;THROW ROLLBACK TRANSACTION; END CATCH;
Check my other blog posts on error/exception handling.