SQL Server is one of the most influential and powerful relational databases of the modern age. Like most relational databases, it uses a flavor of standard SQL called Transact-SQL to execute commands on the server.
However, you will encounter instances where you need to get the error code of the last executed statement. This is where the
@@error function comes into play.
SQL Server @@Error
@@error is part of the SQL Server system functions that allow us to get the error number of the last T-SQL statement executed. This feature is handy when building error-handling techniques in functions and stored procedures.
The following shows the syntax of the function:
The function does not accept any parameters. However, it does return an integer type denoting the error code of the executed statement.
Let us explore the various examples of how we can use this function.
Example 1 - Detecting a Specific Error
This function's first and most common use case is detecting when a specific error occurs. For example, we can use it to detect a constraint error as shown:
USE table_name; GO --- command to execute IF @@ERROR = 547 BEGIN PRINT N'A check constraint violation occurred.'; END GO
In this case, we use the
@@error function to get the error code of the previous statement. if the error code equals
547, we inform the user that a check constraint error violation has occurred.
Example 2 - Rolling Back a Transaction
We can also use this function to detect an error in a given transaction. If there is any error, we perform a rollback and undo the operations carried out by the transaction.
consider an example syntax as shown:
BEGIN TRANSACTION INSERT INTO table_name (table_name) VALUES ('value') IF @@ERROR <> 0 BEGIN ROLLBACK TRANSACTION RETURN END COMMIT TRANSACTION
The example above defines the transaction we wish to carry out. In this case, the transaction inserts the record into the specified table.
We then use the
@@error function to check if any error has occurred when inserting the record. If there is an error, we roll back the transaction.
Otherwise, if there is no error in the transaction, we commit the changes defined in the transaction.
Remember that although this function does work as intended, it is not a replacement for error-handling constructs such as a
try-catch block. Consider using these constructs as they are more intuitive and easy to read and maintain.
In this tutorial, we learned how to use the SQL Server
@@error function to get the error code of the previously executed statement.