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’;

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

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. Errors must have an error number of at least 50000.

THROW

THROW differs from RAISERROR in several ways:

  • Errors raised by THROW are always severity 16.
  • The messages returned by THROW aren't related to any entries in sys.sysmessages.
  • Errors raised by THROW only cause transaction abort when used in conjunction with SET XACT_ABORT ON and the session is terminated.
THROW 50001, 'An Error Occured',0

Capture error codes using @@Error

Most traditional error handling code in SQL Server applications has been created using @@ERROR. Structured exception handling was introduced in SQL Server 2005 and provides a strong alternative to using @@ERROR. 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. Modifying system errors via sp_altermessage is only possible from SQL Server 2005 SP3 or SQL Server 2008 SP1 onwards.