SQL with Manoj

–> Question:

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.
 

Ref Link.