This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Raising Errors in Transact-SQL

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.


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:

Severity RangeDescription
0-10Informational messages only.
11-16Errors relating to user actions that should be able to be fixed by the user. For example, you might use such a level to indicate a validation error, where the user can correct the data. Most errors that you raise will be of severity 16, as 11 to 15 have specific meanings.
17Indicates that the system has run out of a particular resource, where the resource level is customisable by a system administrator.
18A non-fatal error that should be addressed by a system administrator.
19Indicates that a non-configurable limit has been exceeded. This should be investigated by a system administrator. An error of this severity or greater will stop execution of the batch or script. This level can only be raised by a member of the sysadmin role.
20 - 25Indicates a fatal system error that may mean that the database has been damaged. Each value has its own specific meaning.

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.

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.


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:


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.


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:


SET @Number = 42

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