Error Handling (@@Error Vs Try...Catch)

Question

Error Handling (@@Error Vs Try...Catch)

Suresh Arumugam

Hall of Fame

Points: 3370
More actions
May 17, 2010 at 5:38 am

#144709

Hi All,
Recently I tried to tune one of our SP for locking issue by breaking the lengthy transaction to 5 different logical blocks. The SP was written in Sql 2000 and using @@ERROR logic to handle Errors.
After breaking the transactions, I was trying to test what if any transaction block fails and how good the data integrity is. How ever, In case of error (I tried to change the table column name or tried to have update query with in the transaction which will update char value to numeric column), the control is NOT even coming down where the next statement tries to capture @@error and assigning error number to variable since SP execution stops in the errored statment itself .
After seeing the failure of Error handling (using @@Error), I tried with TRY..CATCH and could see the error is caught and I could roll back the transaction.
Now my confusion is how powerful is using @@ERROR and what to choose between TRY..CATCH and @@ERROR?
Please clarify.
Thanks,
Suresh
Regards,
Suresh Arumugam

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

The thing is @@ERROR really doesn't catch errors, it reports them. TRY/CATCH can literally catch, say, a deadlock, and let you retry the query. That difference alone is a great reason to start upgrading code. There are more.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 2

Thanks for the reply. I can understand the need to move to Try..Catch instead of @@ERROR. How ever, I am NOT able to understand what you mean by the below statement.

"@@ERROR really doesn't catch errors, it reports them"

When I tested the SP, control does NOT even come down to @@ERROR (error handling) statement when there was an error in one of the update statement. Then how we can say that @@ERROR statement can report the error?

Could you please explain bit more?

Thanks,

Suresh

Regards,
Suresh Arumugam

Mike01 SSChampion Points: 11393 More actions · Answer 3

@@Error reports the error from the line directly before it. One of the issues with it is the severity of the error. If it is 16, then you will get an error and the @@Error variable or code where you are trying to capture it will get skipped and SQL will just quit. With Try/Catch this behavior is different. You can catch all the errors in this block (even severity 16)

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

Mike nailed it pretty well. I can't add much.

But I'll say this. Imagine a piece of TSQL code that, let's say, inserts data into three tables. You can put a TRY statement at the beginning and a CATCH statement at the end. Alternatively you can capture @@ERROR over and over again, including code using a GOTO to get to the error handler, all messy. Further, each and every statement, including checking @@ERROR resets @@ERROR so you can easily lose the error values.

It's just a mess. TRY/CATCH is a huge leap forward.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 5

Thanks Grant & Mike for the valuable suggestions!

Suresh

Regards,
Suresh Arumugam