SQL Server Get Error of the Last Statement

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

The @@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:

@@ERROR

The function does not accept any parameters. However, it does return an integer type denoting the error code of the executed statement.

Examples

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.

Conclusion

In this tutorial, we learned how to use the SQL Server @@error function to get the error code of the previously executed statement.