Error handling for T-SQL

This topic provides reference information about error handling in SQL Server and Amazon Aurora PostgreSQL, focusing on the differences and similarities between the two systems. You can use this knowledge to understand how error handling mechanisms in SQL Server translate to Aurora PostgreSQL when migrating your database. The topic compares specific error handling features, such as TRY…​CATCH blocks and THROW statements, with their PostgreSQL equivalents.

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, the extensive .NET-like error handling capabilities were added. They included 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.

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.

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

Examples

The following example uses 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'.
Can't insert duplicate key in object 'dbo.ErrorTest'. The duplicate key value is (1).

The following example uses 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'.
Can't 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 run 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:

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

The following example raises 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.

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

Error State Functions

SQL Server provides the following error state functions:

The following example uses 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'. Can't 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'. Can't 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.

PostgreSQL Usage

Amazon Aurora PostgreSQL-Compatible Edition (Aurora PostgreSQL) doesn’t provide native replacement for SQL Server error handling features and options, but it has many comparable options.

To trap the errors, use the BEGIN.. EXCEPTION.. END. By default, any error raised in a PL/pgSQL function block stops running and the surrounding transaction. You can trap and recover from errors using a BEGIN block with an EXCEPTION clause. The syntax is an extension to the normal syntax for a BEGIN block.

Syntax

[ <<label>> ]
[ DECLARE
  declarations ]
BEGIN
  statements
EXCEPTION
  WHEN condition [ OR condition ... ] THEN
    handler_statements
  [ WHEN condition [ OR condition ... ] THEN
    handler_statements
  ... ]
END;

For the preceding example, condition is related to the error or the code. For example:

For all error codes, see PostgreSQL Error Codes in the PostgreSQL documentation.

Throw errors

You can use the PostgreSQL RAISE statement to throw errors. You can combine RAISE with several levels of severity including:

Examples

The following example uses RAISE DEBUG, where DEBUG is the configurable severity level.

SET CLIENT_MIN_MESSAGES = 'debug';

DO $$
BEGIN
RAISE DEBUG USING MESSAGE := 'hello world';
END $$;

DEBUG: hello world
DO

The following example uses the client_min_messages parameter to control the level of messages sent to the client. The default is NOTICE. Use the log_min_messages parameter to control which message levels are written to the server log. The default is WARNING.

SET CLIENT_MIN_MESSAGES = 'debug';

The following example uses EXCEPTION..WHEN…​THEN inside BEGIN and END block to handle dividing by zero violations.

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

INSERT INTO employee values ('John',10);
BEGIN
  SELECT 5/0;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'caught division_by_zero';
  return 0;
END;

Summary

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

Inner
BEGIN
...
EXCEPTION WHEN ... THEN
END

For more information, see Error Handling, Errors and Messages, and When to Log in the PostgreSQL documentation.