What Is SQL Server RAISERROR?

When something goes wrong in your T-SQL, you want to fix the problem quickly with minimal digging around and disruption to users. SQL Server-generated error messages are highly technical and hard to understand, which can make it difficult to isolate issues and can slow down resolution time. Fortunately, DBAs can implement SQL Server RAISERROR as an alternative to SQL Server error messages.

RAISERROR is a SQL Server error handling statement that generates an error message and initiates error processing. RAISERROR can either reference a user-defined message that is stored in the sys.messages catalog view or it can build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY...CATCH construct.

There are several scenarios in which it’s appropriate to use the RAISERROR statement:

• Transact-SQL code troubleshooting
• Returning messages that contain variable text
• Examining data values
• When you need the execution to jump from a TRY block to the associated CATCH block or return error information from the CATCH block to the callers

It’s important to note that when developing new applications, a THROW statement is now preferable to RAISERROR for error handling. But more on that later.

Why Use RAISERROR for SQL Server Error Handling?

There are two primary reasons for choosing RAISERROR over SQL Server-generated error handling:

1. The RAISERROR messages are customizable with regard to level of severity and state
2. They can be written in natural language that is easy to understand

RAISERROR returns error messages to the application in the same format that is generated by SQL Server Database Engine. It allows developers to generate their own error messages, so anyone reading the message will be able to understand what the actual problem is instead of trying to decipher SQL Server’s technical error message. Developers can also set their own severity level, message ID, and state for error messages. 

Using RAISERROR with the TRY...CATCH Construct

TRY...CATCH is an error handling construct that lets you execute code in the TRY section and handle errors in the CATCH section. In general, TRY...CATCH is an effective way to identify many T-SQL errors, but there are a few exceptions. 

This tutorial provides a detailed walkthrough of how to use RAISERROR in conjunction with TRY...CATCH. The author uses an example showing how to use the RAISERROR inside a TRY block to cause execution to jump to the associated CATCH block. Inside the CATCH block, the author demonstrates how to use the RAISERROR to return the error information that invoked the CATCH block. The output displays the message ID, level of severity, and error state.

RAISERROR vs. THROW Error Handling Statements

RAISERROR was introduced in SQL Server 7.0 and has been an effective way to handle T-SQL errors for many years. But if you are developing new apps, Microsoft now recommends using THROW statements instead of RAISERROR. 

As organizations update to SQL Server 2012 and above, RAISERROR is being phased out. In fact, RAISERROR can’t be used in SQL Server 2014’s natively compiled Stored Procedures. THROW is considered an improvement over RAISERROR because it is easier to use. 

Microsoft's SQL Server documentation breaks down the differences between the RAISERROR and THROW error handling statements as follows:

RAISERROR statement

• If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.
• The msg_str parameter can contain printf formatting styles.

The severity parameter specifies the severity of the exception.

THROW statement

• The error_number parameter does not have to be defined in sys.messages.
• The message parameter does not accept printf style formatting.
• There is no severity parameter. The exception severity is always set to 16.

Although RAISERROR’s days may be numbered, it remains a viable error handling option on older versions of SQL Server. Microsoft is pushing users of newer versions of SQL Server (SQL SERVER 2012 and above) to use the THROW statement instead of RAISERROR for implementing error handling. Microsoft hasn’t announced RAISERROR deprecation yet, but it seems likely that it will sooner rather than later.