SQL Server allows to handle errors with
TRY ... CATCH blocks, and provides several statements to get information about the error that is being handled.
TRY and CATCH blocks
BEGIN TRY INSERT INTO ... ; END TRY BEGIN CATCH SELECT 'The INSERT failed with an error' AS message; END CATCH; BEGIN TRY UPDATE ... ; END TRY BEGIN CATCH SELECT 'The UPDATE failed with an error' AS message; END CATCH;
END CATCH) is optional.
Warnings or informational messages with severity lower than 10 are not handled in
GOTO cannot be used to enter a
TRY block or a
Returning information about an error
When we can an error, we can return information about it using the following functions:
ERROR_NUMBER()– The error code.
ERROR_MESSAGE()– The error message.
ERROR_STATE()– The error SQLSTATE.
ERROR_SEVERITY()– The severity of the error.
XACT_STATE()– Whether a transaction is active, and where it can be committed.
- 0: No active transaction.
- -1: A transaction is active, but the error prevents it from being committed. It can only be rolled back.
- 1: A transaction is active and it can be committed. We can decide whether it’s better to commit it or roll it back.
The following functions tell us where the error occurred:
ERROR_LINE()– The line where the error occurred.
ERROR_PROCEDURE()– The stored procedure, stored function or trigger where the error occurred, or
BEGIN TRY INSERT INTO ... ; END TRY BEGIN CATCH IF XACT_STATE() = 1 OR XACT_STATE() = -1 ROLLBACK TRANSACTION; SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message; END CATCH;
It is also possible to do something in the
CATCH block, and then re-throw the same error using the above functions in combination with
RAISERROR(). For more information about this, see SQL Server: Raise errors and warnings.
See the links to the SQL Server in the text above.
See also SQL Server: Raise errors and warnings.