@@ERROR vs. TRY...CATCH

Using @@ERROR as the primary means of detecting errors leads to a very different style of error-handling code than that which is used with TRY…CATCH constructs.

·         @@ERROR must be either tested or saved after every Transact-SQL statement because a developer cannot predict in advance which statement might generate an error. This doubles the number of Transact-SQL statements that must be coded to implement a given piece of logic.

·         TRY…CATCH constructs are much simpler. A block of Transact-SQL statements is bounded by BEGIN TRY and END TRY statements, and then one CATCH block is written to handle errors that might be generated by that block of statements.

Outside of a CATCH block, @@ERROR is the only part of a Database Engine error available within the batch, stored procedure, or trigger that generated the error. All other parts of the error, such as its severity, state, and message text containing replacement strings (object names, for example) are returned only to the application in which they can be processed using the API error-handling mechanisms. If the error invokes a CATCH block, the system functions ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_NUMBER, ERROR_SEVERITY, and ERROR_STATE can be used.