Implement T-SQL error handling

  • 3 minutes

An error indicates a problem or notable issue that arises during a database operation. Errors can be generated by the SQL Server Database Engine in response to an event or failure at the system level; or you can generate application errors in your Transact-SQL code.

Elements of database engine errors

Whatever the cause, every error is composed of the following elements:

  • Error number - Unique number identifying the specific error.
  • Error message - Text describing the error.
  • Severity - Numeric indication of seriousness from 1 to 25.
  • State - Internal state code for the database engine condition.
  • Procedure - The name of the stored procedure or trigger in which the error occurred.
  • Line number - Which statement in the batch or procedure generated the error.

System errors

System errors are predefined, and you can view them in the sys.messages system view. When a system error occurs, SQL Server may take automatic remedial action, depending on the severity of the error. For example, when a high-severity error occurs, SQL Server may take a database offline or even stop the database engine service.

Custom errors

You can generate errors in Transact-SQL code to respond to application-specific conditions or to customize information sent to client applications in response to system errors. These application errors can be defined inline where they're generated, or you can predefine them in the sys.messages table alongside the system-supplied errors. The error numbers used for custom errors must be 50001 or above.

To add a custom error message to sys.messages, use sp_addmessage. The user for the message must be a member of the sysadmin or serveradmin fixed server roles.

This is the sp_addmessage syntax:

sp_addmessage [ @msgnum= ] msg_id , [ @severity= ] severity , [ @msgtext= ] 'msg' 
     [ , [ @lang= ] 'language' ] 
     [ , [ @with_log= ] { 'TRUE' | 'FALSE' } ] 
     [ , [ @replace= ] 'replace' ]

Here is an example of a custom error message using this syntax:

sp_addmessage 50001, 10, N’Unexpected value entered’;

Note

sp_addmessage is supported on SQL Server only. On Azure SQL Database and Azure SQL Managed Instance, sp_addmessage isn't supported, so you can't reference a msg_id greater than 50000. On those platforms, use inline RAISERROR with a message string, or THROW combined with FORMATMESSAGE(), instead.

In addition, you can define custom error messages, members of the sysadmin server role can also use an additional parameter, @with_log. When set to TRUE, the error will also be recorded in the Windows Application log. Any message written to the Windows Application log is also written to the SQL Server error log. Be judicious with the use of the @with_log option because network and system administrators tend to dislike applications that are “chatty” in the system logs. However, if the error needs to be trapped by an alert, the error must first be written to the Windows Application log.

Note

Raising system errors is not supported.

Messages can be replaced without deleting them first by using the @replace = ‘replace’ option.

The messages are customizable and different ones can be added for the same error number for multiple languages, based on a language_id value.

Note

English messages are language_id 1033.

Raise errors using RAISERROR

Note

RAISERROR is deprecated for new development. Use THROW for new T-SQL code. RAISERROR remains useful for legacy code, for raising severity levels above 16 (which THROW can't do directly), and for printf-style dynamic message formatting (the modern alternative is FORMATMESSAGE() combined with THROW).

Both PRINT and RAISERROR can be used to return information or warning messages to applications. RAISERROR allows applications to raise an error that could then be caught by the calling process.

RAISERROR

The ability to raise errors in T-SQL makes error handling in the application easier, because it's sent like any other system error. RAISERROR is used to:

  • Help troubleshoot T-SQL code.
  • Check the values of data.
  • Return messages that contain variable text.

Note

Using a PRINT statement is similar to raising an error of severity 10.

Here is an example of a custom error message using RAISERROR.

RAISERROR (N'%s %d', -- Message text,
    10, -- Severity,
    1, -- State,
    N'Custom error message number',
    2)

When triggered, it returns:

Custom error message number 2

In the previous example, %d is a placeholder for a number and %s is a placeholder for a string. In addition, you should note that a message number wasn't mentioned. When errors with message strings are raised using this syntax, they always have error number 50000.

Raise errors using THROW

The THROW statement offers a simpler method of raising errors in code and is the recommended approach for new T-SQL development. Errors must have an error number of at least 50000.

THROW

THROW differs from RAISERROR in several ways:

  • When THROW raises a new exception, the severity is always 16. When THROW is used to rethrow an existing exception (parameterless THROW inside a CATCH block), the original exception's severity is preserved—this is why parameterless THROW is preferred for rethrowing system errors from a CATCH block.
  • The messages returned by THROW aren't related to any entries in sys.messages.
  • THROW honors SET XACT_ABORT. When SET XACT_ABORT ON is active, an exception raised by THROW automatically rolls back the current transaction. RAISERROR doesn't honor SET XACT_ABORT—this is one of the main reasons THROW is preferred for new development.

Important

The statement immediately before THROW must end with a semicolon (;), otherwise you get a syntax error. This is a common stumbling block when adopting THROW in existing code that omits semicolons.

; THROW 50001, 'An error occurred', 1;

Capture error codes using @@Error

Most traditional error handling code in SQL Server applications has been created using @@ERROR. Structured exception handling provides a more powerful alternative to using @@ERROR and is the recommended approach for new T-SQL code. It will be discussed in the next lesson. A large amount of existing SQL Server error handling code is based on @@ERROR, so it is important to understand how to work with it.

@@ERROR

@@ERROR is a system variable that holds the error number of the last error that has occurred. One significant challenge with @@ERROR is that the value it holds is quickly reset as each additional statement is executed.

For example, consider the following code:

RAISERROR(N'Message', 16, 1);
IF @@ERROR <> 0
PRINT 'Error=' + CAST(@@ERROR AS VARCHAR(8));
GO

You might expect that, when the code is executed, it would return the error number in a printed string. However, when the code is executed, it returns:

Msg 50000, Level 16, State 1, Line 1
Message
Error=0

The error was raised but the message printed was “Error=0”. In the first line of the output, you can see that the error, as expected, was actually 50000, with a message passed to RAISERROR. This is because the IF statement that follows the RAISERROR statement was executed successfully and caused the @@ERROR value to be reset. For this reason, when working with @@ERROR, it's important to capture the error number into a variable as soon as it's raised, and then continue processing with the variable.

Look at the following code that demonstrates this:

DECLARE @ErrorValue int;
RAISERROR(N'Message', 16, 1);
SET @ErrorValue = @@ERROR;
IF @ErrorValue <> 0
PRINT 'Error=' + CAST(@ErrorValue AS VARCHAR(8));

When this code is executed, it returns the following output:

Msg 50000, Level 16, State 1, Line 2
Message
Error=50000

The error number is correctly reported now.

Centralizing error handling

One other significant issue with using @@ERROR for error handling is that it's difficult to centralize within your T-SQL code. Error handling tends to end up scattered throughout the code. It would be possible to centralize error handling using @@ERROR to some extent, by using labels and GOTO statements. However, this would be frowned upon by most developers' today as a poor coding practice.

Create error alerts

For certain categories of errors, administrators might create SQL Server alerts, because they wish to be notified as soon as these occur. This can even apply to user-defined error messages. For example, you might want to raise an alert whenever a transaction log fills. Alerting is commonly used to bring high severity errors (such as severity 19 or above) to the attention of administrators.

Raising alerts

Alerts can be created for specific error messages. The alerting service works by registering itself as a callback service with the event logging service. This means that alerts only work on logged errors.

There are two ways to make an error raise an alert—you can use the WITH LOG option when raising the error or the message can be altered to make it logged by executing sp_altermessage. The WITH LOG option affects only the current statement. Using sp_altermessage changes the error behavior for all future use.