What is the difference of using TRY...CATCH and @@ERROR in SQL Server?

Question

While writing transaction SQL operations, I often use @@ERROR to find out whether some issue happened during the transaction or not. But I really don't know what is it's advantages and disadvantages over exception handling with TRY...CATCH, or which one is better. Please clarify difference between those and the scenarios at which these are useful.

Thanks in advance, Vinu.

Answer 1

@@ERROR will be populated as soon as an error occurs but if there is another statement executing after the error occurred @@ERROR will reset its value to NULL, so anticipating where an error can possibly occur and storing its values to a variable etc. etc. seems a lot of hassle.

Where as if you are making use of TRY...CATCH block, as soon as an error occurs in the TRY block your control jumps to the CATCH block and and you can make use of the system Error Functions , which can give you detailed information about the error. If no error occurs then the control never enters the CATCH block.

Some of them system error functions are:

ERROR_LINE()
EROR_MESSAGE()
ERROR_Severity()
ERROR_PROCEDURE()
ERROR_STATE()

Therefore I personally think using TRY...CATCH makes your life a lot easier as a developer.

Answer 2

Well, you are correct that they have some things in common, such as - they both come in handy for error handling and troubleshooting, but at the bottom line they are completely different:

• @@ERROR will give you the error number for the last Transact-SQL statement executed. i.e. It will give information about the error that have occurred.
• TRY-CATCH Implements error handling. once an error happens, the flow will be interrupted and the code in the CATCH statement will be executed.

Personally, I usually use TRY-CATCH in order to stop the flow of execution upon an error, and use ERROR_MESSAGE() and ERROR_NUMBER() for information about the nature of the error.

I think this answer demonstrates it quite well.