SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Suresh Arumugam
Suresh Arumugam
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2104 Visits: 404
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
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)

Group: General Forum Members
Points: 237316 Visits: 33639
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Suresh Arumugam
Suresh Arumugam
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2104 Visits: 404
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
Mike01
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5116 Visits: 1563
@@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
Grant Fritchey
SSC Guru
SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)SSC Guru (237K reputation)

Group: General Forum Members
Points: 237316 Visits: 33639
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Suresh Arumugam
Suresh Arumugam
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2104 Visits: 404
Thanks Grant & Mike for the valuable suggestions!

Suresh

Regards,
Suresh Arumugam
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search