Vinu Vinu - 22 days ago 8
SQL Question

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

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 Source

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