SQL Server’s RAISERROR Function

This is Part 4 of a series of blog posts by Data Education founder Adam Machanic on errors and exceptions in Microsoft SQL Server. The posts will cover everything from the TRY/CATCH syntax to the delicate relationship between transactions and exceptions. In Part 1, Adam gave a basic explanation of the difference between errors and exceptions. In Part 2, he examined types of exceptions. In Part 3, Adam broke down the parts of the dreaded error message. In this post, he takes a steely-eyed look at the RAISERROR function.

In addition to the exceptions that SQL Server itself throws, users can raise exceptions within T-SQL by using a function called RAISERROR. The general form for this function is as follows:

RAISERROR ( { msg_id | msg_str | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]

The first argument can be an ad hoc message in the form of a string or variable, or a valid error number from the message_id column of sys.messages. If a string is specified, it can include format designators that can then be filled using the optional arguments specified at the end of the function call.

The second argument, severity, can be used to enforce some level of control over the behavior of the exception, similar to what SQL Server uses error levels for. For the most part, the same exception ranges apply: exception levels between 1 and 10 result in a warning, levels between 11 and 18 are considered normal user errors, and those above 18 are considered serious and can only be raised by members of the sysadmin fixed server role. User exceptions raised over level 20, just like those raised by SQL Server, cause the connection to break. Beyond these ranges, there is no real control afforded to user-raised exceptions, and all are considered to be statement level—this is even true with XACT_ABORT set.

The state argument can be any value between 1 and 127, and has no effect on the behavior of the exception. It can be used to add additional coded information to be carried by the exception—but it’s probably just as easy to add that data to the error message itself in most cases. I generally use a value of 1 for state when raising custom exceptions.

The simplest way to use RAISERROR is to pass in a string containing an error message, and set the appropriate error level. For general exceptions, I usually use 16:

RAISERROR('General exception', 16, 1)

This results in the following output:

Msg 50000, Level 16, State 1, Line 1
General exception

Note that the error number used in this case is 50000, which is the generic user-defined error number that will be used whenever passing in a string for the first argument to RAISERROR.

Formatting Error Messages

When defining error messages, it is generally useful to format the text in some way. For example, think about how you might write code to work with a number of product IDs, dynamically retrieved, in a loop. You might have a local variable called @ProductId, which contains the current ID that the code is working with. You might wish to define a custom exception that should be thrown when a problem occurs—and it would probably be a good idea to return the current value of @ProductId along with the error message.

In this case, there are a couple of ways of sending back the data with the exception. The first is to dynamically build an error message string:

SET @ProductId = 100

/* ... problem occurs ... */

DECLARE @ErrorMessage VARCHAR(200)
SET @ErrorMessage =
    'Problem with ProductId ' + CONVERT(VARCHAR, @ProductId)

RAISERROR(@ErrorMessage, 16, 1)

Executing this batch results in the following output:

Msg 50000, Level 16, State 1, Line 10
Problem with ProductId 100

While this works for this case, dynamically building up error messages is not the most elegant development practice. A step in a better direction is to make use of a format designator and to pass @ProductId as an optional parameter:

SET @ProductId = 100

/* ... problem occurs ... */

RAISERROR('Problem with ProductId %i', 16, 1, @ProductId)

Executing this batch results in the same output as before, but requires quite a bit less code, and you don’t have to worry about defining extra variables or building up messy conversion code. The %i embedded in the error message is a format designator that means “integer.” The other most commonly used format designator is %s, for “string.”

You can embed as many designators as necessary in an error message, and they will be substituted in the order in which optional arguments are appended:

SET @ProductId1 = 100

SET @ProductId2 = 200

SET @ProductId3 = 300

/* ... problem occurs ... */

RAISERROR('Problem with ProductIds %i, %i, %i',
    16, 1, @ProductId1, @ProductId2, @ProductId3)

This results in the following output:

Msg 50000, Level 16, State 1, Line 12
Problem with ProductIds 100, 200, 300

Fun note: Readers familiar with C programming will notice that the format designators used by RAISERROR are the same as those used by the C language’s printf function.

Creating Persistent Custom Error Messages

Formatting messages using format designators instead of building up strings dynamically is a step in the right direction, but it does not solve one final problem: What if you need to use the same error message in multiple places? You could simply use the same exact arguments to RAISERROR in each routine in which the exception is needed, but that might cause a maintenance headache if you ever needed to change the error message. In addition, each of the exceptions would only be able to use the default user-defined error number, 50000, making programming against these custom exceptions much more difficult.

Luckily, SQL Server takes care of these problems quite nicely, by providing a mechanism by which custom error messages can be added to sys.messages. Exceptions using these error messages can then be raised by using RAISERROR and passing in the error number as the first parameter.

To create a persistent custom error message, use the sp_addmessage stored procedure. This stored procedure allows the user to specify custom messages for message numbers over 50000. In addition to an error message, users can specify a default severity. Messages added using sp_addmessage are scoped at the server level, so if you have multiple applications hosted on the same server, be aware of whether they define custom messages and whether there is any overlap—you may need to set up a new instance of SQL Server for one or more of the applications in order to allow them to create their exceptions. When developing new applications that use custom messages, try to choose a random range in which to create your messages, in order to avoid overlaps with other applications in shared environments. Remember that you can use any number between 50000 and 2147483647, and you don’t need to stay in the 50000 range.

Adding a custom message is as easy as calling sp_addmessage and defining a message number and the message text. The following T-SQL defines the message from the previous section as error message number 50005:

EXEC sp_addmessage
    @msgnum = 50005,
    @severity = 16,
    @msgtext = 'Problem with ProductIds %i, %i, %i'

Once this T-SQL is executed, an exception can be raised using this error message, by calling RAISERROR with the correct error number:

RAISERROR(50005, 15, 1, 100, 200, 300)

This causes the following output to be sent back to the client:

Msg 50005, Level 15, State 1, Line 1
Problem with ProductIds 100, 200, 300

Note that when calling RAISERROR in this case, severity 15 was specified, even though the error was defined with severity 16. This brings up an important point about severities of custom errors: Whatever severity is specified in the call to RAISERROR will override the severity that was defined for the error. However, the default severity will be used if you pass a negative value for that argument to RAISERROR:

RAISERROR(50005, -1, 1, 100, 200, 300)

This produces the following output (notice that Level is now 16, as defined):

Msg 50005, Level 16, State 1, Line 1
Problem with ProductIds 100, 200, 300

It is recommended that, unless you are overriding the severity for a specific reason, you always use -1 for the severity argument when raising a custom exception.

Changing the text of an exception once defined is also easy using sp_addmessage. To do so, pass the optional @Replace argument, setting its value to 'Replace', as in the following T-SQL:

EXEC sp_addmessage
    @msgnum = 50005,
    @severity = 16,
    @msgtext = 'Problem with ProductId numbers %i, %i, %i',
    @Replace = 'Replace'

Yet another fun note: In addition to being able to add a message and set a severity, sp_addmessage supports localization of messages for different languages. The examples here do not show localization; instead, messages will be created for the user’s default language.

Logging User-Thrown Exceptions

Another useful feature of RAISERROR is the ability to log messages to SQL Server’s error log. This can come in handy, especially when working with automated code, such as T-SQL running in SQL Server Agent jobs. In order to log any exception, use the WITH LOG option of the RAISERROR function, as in the following T-SQL:

RAISERROR('This will be logged.', 16, 1) WITH LOG

Note that specific access rights are required to log an error. The user executing the RAISERROR function must either be a member of the sysadmin fixed server role or have ALTER TRACE permissions.

Phew. That’s it for now on RAISERROR. Coming soon: Fun with exception handling!

1 Comment

  1. Greg LucasGreg LucasDec 12, 2011
    11:19 PM

    Adam, great post and a good series. I blogged ages ago about getting RAISERROR to work like PRINT i.e. without the “Message 50000 Level …” line