SQL 2005+

Raising Errors in Transact-SQL

by Richard Carr, published at http://www.blackwasp.co.uk/SQLRaiserror.aspx

When developing reusable scripts, such as stored procedures and triggers, it is often necessary to raise errors. With the RAISERROR command, it is possible to create errors with varying severity and customised messages.

Using RAISERROR

When you are creating stored procedures, triggers or user-defined functions, you may need to raise errors under some conditions. For example, you might perform a complex integrity check during execution of a trigger, which may be too complicated to cover with a foreign key or check constraint. When the check fails, you might wish to roll back the transaction and raise an error that can be captured in a TRY/CATCH block.

To raise an error you use the RAISERROR command. Note the missing 'E' in the command's name. Each error requires a minimum of three pieces of information. They are the message, the severity level and the state code.

The message element is provided as the first argument of the command. This can be a plain string, surrounded by apostrophes, or can include special placeholders for inserted values. The maximum error length is 2,047 characters. If the message is larger than this, it is truncated to 2,044 characters and an ellipsis (...) is appended.

The severity level specifies how serious the error is. The following ranges of level are used:

The state value, which is the third parameter of the command, is a number between zero and 255. It does not affect the meaning of the error but is useful for grouping errors. For example, you might decide to use the same state code for a number of errors that are raised in response to similar problems.

The following example raises an error to tell the user that something bad happened.

RAISERROR('Something bad happened', 16, 1)

If you execute the above command in a query window you'll see the following output:

Msg 50000, Level 16, State 1, Line 1
Something bad happened

Message Formatting

Often you will want to customise the error with data to provide more useful feedback. For example, if the user provided an invalid value, you might want that value to appear within the message. You could build the message in stages to achieve this but in many cases you can get the same results with placeholders. These are identified with a percentage symbol (%) and a special code within the message. The placeholders are replaced at runtime with information from additional arguments.

Inserting Strings into the Message

If the information to be inserted into the message is held as a string data type, you can use the %s placeholder. This is shown in the example below. Note that the severity level and state code are still included as the second and third arguments. The fourth argument contains the value that replaces the placeholder.

DECLARE @Hello VARCHAR(10)
SET @Hello = 'world'

RAISERROR('Hello, %s.', 16, 1, @Hello)  -- Hello, world.

Inserting Numbers into the Message

Inserting an integer is similar to adding a string. Instead of using %s, you can use either %d or %i for signed integers and %u for unsigned ones. This is demonstrated below. Note that the order of the additional value parameters must match the order of the placeholders in the message.

DECLARE @Hello VARCHAR(10)
DECLARE @Number INT

SET @Hello = 'world'
SET @Number = 42

RAISERROR('Hello, %s number %d.', 16, 1, @Hello, @Number)  -- Hello, world number 42.

For signed numbers a negative value is prefixed with a minus (-) but a positive value has no prefix. To indicate that you wish to include a plus (+) symbol for these values, add a plus after the percentage sign, as shown below:

DECLARE @Hello VARCHAR(10)
DECLARE @Number INT

SET @Hello = 'world'
SET @Number = 42

RAISERROR('Hello, %s number %+d.', 16, 1, @Hello, @Number)  -- Hello, world number +42.

For unsigned numbers you can also output the value using either octal or hexadecimal. For octal use %o. For hexadecimal output use %x or %X. The case of the placeholder's letter determines whether the hexadecimal letters will be capitalised.

DECLARE @Number INT

SET @Number = 42

RAISERROR('Error number %d (%X).', 16, 1, @Number, @Number)  -- Error number 42 (2A).

It is common to prefix hexadecimal values with 0x. To automatically insert this prefix, use a hash symbol (#) after the percentage character, as follows:

DECLARE @Number INT

SET @Number = 42

RAISERROR('Error number %d (%#X).', 16, 1, @Number, @Number)  -- Error number 42 (0X2A).