@@ERROR

This variable is used for a basic error handling mechanism in SQL Server earlier days (before SQL 2005). This variable is used to capture the error code of an immediate previous statement if any. If there is an error, it will return the error code else 0. It is to be noted that any statement will reset the value including the same statement. So ideally, If you want to capture for a longer scope, the immediate @@ERROR code can be assigned to a variable.

Let us quickly look at a simple example as below.

Select 1/0
Select @@ERROR, @@TOTAL_ERRORS
Select @@ERROR, @@TOTAL_ERRORS

In the above code, the first statement is throwing the below error.

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

And the first @@ERROR statement will capture the error code, where as while executing the second @@ERROR, the value is rest to 0.

Once you captured the error code, we can verify the message it later from sys.messages system table as well.

@@TOTAL_ERRORS

This variable sounds similar to the above one, however, this has no relation with @@ERROR. This variable returns the number of disk write errors encountered by SQL Server since SQL Server last started/restarted.

I could not see lots of use cases if this variable. If you come across and already experienced, please feel free share those information as a comment.

If you enjoyed this blog post, please feel free to share it with your friends!