Error handling for T-SQL

This topic provides reference content comparing error handling approaches between Microsoft SQL Server 2019 and Amazon Aurora MySQL. You can gain insights into the differences in error handling paradigms, syntax, and capabilities between these two database systems.

SQL Server Usage

SQL Server error handling capabilities have significantly improved throughout the years. However, previous features are retained for backward compatibility.

Before SQL Server 2008, only very basic error handling features were available. RAISERROR was the primary statement used for error handling.

Starting from SQL Server 2008, SQL Server has added extensive .NET-like error handling capabilities including TRY/CATCH blocks, THROW statements, the FORMATMESSAGE function, and a set of system functions that return metadata for the current error condition.

TRY/CATCH Blocks

TRY/CATCH blocks implement error handling similar to Microsoft Visual C# and Microsoft Visual C++. TRY …​ END TRY statement blocks can contain T-SQL statements.

If an error is raised by any of the statements within the TRY …​ END TRY block, the run stops and is moved to the nearest set of statements that are bounded by a CATCH …​ END CATCH block.

Syntax

BEGIN TRY
<Set of SQL Statements>
END TRY
BEGIN CATCH
<Set of SQL Error Handling Statements>
END CATCH

THROW

The THROW statement raises an exception and transfers run of the TRY …​ END TRY block of statements to the associated CATCH …​ END CATCH block of statements.

Throw accepts either constant literals or variables for all parameters.

Syntax

THROW [Error Number>, <Error Message>, < Error State>] [;]

Examples

Use TRY/CATCH error blocks to handle key violations.

CREATE TABLE ErrorTest (Col1 INT NOT NULL PRIMARY KEY);

BEGIN TRY
    BEGIN TRANSACTION
        INSERT INTO ErrorTest(Col1) VALUES(1);
        INSERT INTO ErrorTest(Col1) VALUES(2);
        INSERT INTO ErrorTest(Col1) VALUES(1);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    THROW; -- Throw with no parameters = RETHROW
END CATCH;

(1 row affected)
(1 row affected)
(0 rows affected)
Msg 2627, Level 14, State 1, Line 7
Violation of PRIMARY KEY constraint 'PK__ErrorTes__A259EE54D8676973'.
Cannot insert duplicate key in object 'dbo.ErrorTest'. The duplicate key value is (1).

Use THROW with variables.

BEGIN TRY
BEGIN TRANSACTION
INSERT INTO ErrorTest(Col1) VALUES(1);
INSERT INTO ErrorTest(Col1) VALUES(2);
INSERT INTO ErrorTest(Col1) VALUES(1);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
DECLARE @CustomMessage VARCHAR(1000),
    @CustomError INT,
    @CustomState INT;
SET @CustomMessage = 'My Custom Text ' + ERROR_MESSAGE();
SET @CustomError = 54321;
SET @CustomState = 1;
THROW @CustomError, @CustomMessage, @CustomState;
END CATCH;

(0 rows affected)
Msg 54321, Level 16, State 1, Line 19
My Custom Text Violation of PRIMARY KEY constraint 'PK__ErrorTes__A259EE545CBDBB9A'.
Cannot insert duplicate key in object 'dbo.ErrorTest'. The duplicate key value is (1).

RAISERROR

The RAISERROR statement is used to explicitly raise an error message, similar to THROW. It causes an error state for the running session and forwards run to either the calling scope or, if the error occurred within a TRY …​ END TRY block, to the associated CATCH …​ END CATCH block. RAISERROR can reference a user-defined message stored in the sys.messages system table or can be used with dynamic message text.

The key differences between THROW and RAISERROR are:

Syntax

RAISERROR (<Message ID>|<Message Text> ,<Message Severity> ,<Message State>
[WITH option [<Option List>]])

Example

Raise a custom error.

RAISERROR (N'This is a custom error message with severity 10 and state 1.', 10, 1)

FORMATMESSAGE

FORMATMESSAGE returns a sting message consisting of an existing error message in the sys.messages system table, or from a text string, using the optional parameter list replacements. The FORMATMESSAGE statement is similar to the RAISERROR statement.

Syntax

FORMATMESSAGE (<Message Number> | <Message String>, <Parameter List>)

Error State Functions

SQL Server provides the following error state functions:

Examples

Use error state functions within a CATCH block.

CREATE TABLE ErrorTest (Col1 INT NOT NULL PRIMARY KEY);

BEGIN TRY;
    BEGIN TRANSACTION;
        INSERT INTO ErrorTest(Col1) VALUES(1);
        INSERT INTO ErrorTest(Col1) VALUES(2);
        INSERT INTO ErrorTest(Col1) VALUES(1);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT ERROR_LINE(),
        ERROR_MESSAGE(),
        ERROR_NUMBER(),
        ERROR_PROCEDURE(),
        ERROR_SEVERITY(),
        ERROR_STATE(),
        @@Error;
THROW;
END CATCH;

6
Violation of PRIMARY KEY constraint 'PK__ErrorTes__A259EE543C8912D8'.
Cannot insert duplicate key in object 'dbo.ErrorTest'.
The duplicate key value is (1).
2627
NULL
14
1
2627

(1 row affected)
(1 row affected)
(0 rows affected)
(1 row affected)
Msg 2627, Level 14, State 1, Line 25
Violation of PRIMARY KEY constraint 'PK__ErrorTes__A259EE543C8912D8'.
Cannot insert duplicate key in object 'dbo.ErrorTest'.
The duplicate key value is (1).

For more information, see RAISERROR (Transact-SQL), TRY…​CATCH (Transact-SQL), and THROW (Transact-SQL) in the SQL Server documentation.

MySQL Usage

Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) offers a rich error handling framework with a different paradigm than SQL Server. The Aurora MySQL terminology is:

Errors in Aurora MySQL are identified by the follow items:

DECLARE …​ CONDITION

The DECLARE …​ CONDITION statement declares a named error condition and associates the name with a condition that requires handling. You can reference this declared name in subsequent DECLARE …​ HANDLER statements.

Syntax

DECLARE <Condition Name> CONDITION
FOR <Condition Value>

<Condition Value> = <MySQL Error Code> | <SQLSTATE [VALUE] <SQLState Value>

Examples

Declare a condition for MySQL error 1051 (Unknown table error).

DECLARE TableDoesNotExist CONDITION FOR 1051;

Declare a condition for SQL State 42S02 (Base table or view not found) .

DECLARE TableDoesNotExist CONDITION FOR SQLSTATE VALUE '42S02';

DECLARE …​ HANDLER

A HANDLER object defines the actions or statements to be ran when a CONDITION arises. The handler object may be used to CONTINUE or EXIT the run.

The condition may be a previously defined condition using the DECLARE …​ CONDITION statement or an explicit condition for one of the following items:

Syntax

DECLARE {CONTINUE | EXIT | UNDO}
HANDLER FOR
<MySQL Error Code> |
<SQLSTATE [VALUE] <SQLState Value> |
<Condition Name> |
SQLWARNING |
NOT FOUND |
SQLEXCEPTION
<Statement Block>

Examples

Declare a handler to ignore warning messages and continue run by assigning an empty statement block.

DECLARE CONTINUE HANDLER
FOR SQLWARNING BEGIN END

Declare a handler to EXIT upon duplicate key violation and log a message to a table.

DECLARE EXIT HANDLER
FOR SQLSTATE '23000'
BEGIN
    INSERT INTO MyErrorLogTable
        VALUES(NOW(), CURRENT_USER(), 'Error 23000')
END

GET DIAGNOSTICS

Each run of an SQL statement produces diagnostic information that is stored in the diagnostics area. The GET DIAGNOSTICS statement enables users to retrieve and inspect this information.

The GET DIAGNOSTICS statement is typically used in the handler code within a stored routine. GET CURRENT DIAGNOSTICS is permitted outside the context of a handler to check the run result of an SQL statement.

The CURRENT keyword causes retrieval of the current diagnostics area. The STACKED keyword causes retrieval of the information from the second diagnostics area. The second diagnostic area is only available if the current context is within a code block of a condition handler. The default is CURRENT.

Syntax

GET [CURRENT | STACKED] DIAGNOSTICS
<@Parameter = NUMBER | ROW_COUNT>
|
CONDITION <Condition Number> <@Parameter = CLASS_ORIGIN | SUBCLASS_ORIGIN | RETURNED_
SQLSTATE | MESSAGE_TEXT | MYSQL_ERRNO | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA |
CONSTRAINT_NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME>

Example

Retrieve SQLSTATE and MESSAGE_TEXT from the diagnostic area for the last statement that you ran.

GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT

SIGNAL/RESIGNAL

The SIGNAL statement is used to raise an explicit condition or error. It can be used to provide full error information to a handle, to an outer scope of run, or to the SQL client. The SIGNAL statement enables explicitly defining the error’s properties such as error number, SQLSTATE value, message, and so on.

The difference between SIGNAL and RESIGNAL is that RESIGNAL is used to pass on the error condition information available during the run of a condition handler within a compound statement inside a stored routine or an event. RESIGNAL can be used to change none, some, or all the related condition information before passing it for processing in the next calling scope of the stack.

Syntax

SIGNAL | RESIGNAL <SQLSTATE [VALUE] sqlstate_value | <Condition Name>
[SET <Condition Information Item Name> = <Value> [,...n]]
<Condition Information Item Name> = CLASS_ORIGIN | SUBCLASS_ORIGIN | RETURNED_SQLSTATE
| MESSAGE_TEXT | MYSQL_ERRNO | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_
NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME

Examples

Raise an explicit error with SQLSTATE 55555.

SIGNAL SQLSTATE '55555'

Re-raise an error with an explicit MySQL error number.

RESIGNAL SET MYSQL_ERRNO = 5

Migration Considerations

The basic operations of raising, processing, responding, and obtaining metadata is similar in nature for most relational database management systems. The technical aspects of rewriting the code to use different types of objects isn’t difficult.

In SQL Server, there can only be one handler, or CATCH code block, that handles exceptions for a given statement. In Aurora MySQL, multiple handler objects can be declared. A condition may trigger more than one handler. Be sure the correct handlers are ran as expected, especially when there are multiple handlers. The following sections provides rules to help establish your requirements.

Handler Scope

A handler can be specific or general. Specific handlers are handlers defined for a specific MySQL error code, SQLSTATE, or a condition name. Therefore, only one type of event will trigger a specific handler. General handlers are handlers defined for conditions in the SQLWARNING, SQLEXCEPTION, or NOT FOUND classes. More than one event may trigger the handler.

A handler is in scope for the block in which it is declared. It can’t be triggered by conditions occurring outside the block boundaries.

A handler declared in a BEGIN …​ END block is in scope for the SQL statements that follow the handler declaration.

One or more handlers may be declared in different or the same scopes using different specifications. For example, a specific MySQL error code handler may be defined in an outer code block while a more general SQLWARNING handler is defined within an inner code block. Specific MySQL error code handlers and a general SQLWARNING class handler may exist within the same code block.

Handler Choice

Only one handler is triggered for a single event. Aurora MySQL decides which handler should be triggered. The decision regarding which handler should be triggered as a response to a condition depends on the handler’s scope and value. It also depends on whether or not other handlers are present that may be more appropriate to handle the event.

When a condition occurs in a stored routine, the server searches for valid handlers in the current BEGIN …​ END block scope. If none are found, the engine searches for handlers in each successive containing BEGIN …​ END code block scope. When the server finds one or more applicable handlers at any given scope, the choice of which one to trigger is based on the following condition precedence:

Multiple applicable handlers with the same precedence may exist for a condition. For example, a statement could generate several warnings having different error codes. There may exist a specific MySQL error handler for each. In such cases, the choice is non-deterministic. Different handlers may be triggered at different times depending on the circumstances.

Summary

The following table identifies similarities, differences, and key migration considerations.

For more information, see The MySQL Diagnostics Area and Condition Handling in the MySQL documentation.