Home > SQL Server Questions > Most effective way to write SQL Statement to Catch and Reverse errors during Query execution – MSDN TSQL forum

Most effective way to write SQL Statement to Catch and Reverse errors during Query execution – MSDN TSQL forum


–> 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.


  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: