RAISERROR (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
Note
The RAISERROR
statement doesn't honor SET XACT_ABORT
. New applications should use THROW
instead of RAISERROR
.
Generates an error message and initiates error processing for the session. RAISERROR
can either reference a user-defined message stored in the sys.messages
catalog view, or 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. New applications should use THROW instead.
Transact-SQL syntax conventions
Syntax
Syntax for SQL Server, Azure SQL Database, and Azure SQL Managed Instance:
RAISERROR ( { msg_id | msg_str | @local_variable }
{ , severity , state }
[ , argument [ , ...n ] ] )
[ WITH option [ , ...n ] ]
Syntax for Azure Synapse Analytics and Parallel Data Warehouse:
RAISERROR ( { msg_str | @local_variable }
{ , severity , state }
[ , argument [ , ...n ] ] )
[ WITH option [ , ...n ] ]
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
msg_id
A user-defined error message number stored in the sys.messages
catalog view using sp_addmessage
. Error numbers for user-defined error messages should be greater than 50000
. When msg_id isn't specified, RAISERROR
raises an error message with an error number of 50000
.
msg_str
A user-defined message with formatting similar to the printf
function in the C standard library. The error message can have a maximum of 2,047 characters. If the message contains 2,048 or more characters, only the first 2,044 are displayed; an ellipsis is added to indicate that the message is truncated. Substitution parameters consume more characters than the output shows because of internal storage behavior. For example, the substitution parameter of %d
with an assigned value of 2
actually produces one character in the message string but also internally takes up three extra characters of storage. This storage requirement decreases the number of available characters for message output.
When msg_str is specified, RAISERROR
raises an error message with an error number of 50000
.
msg_str is a string of characters with optional embedded conversion specifications. Each conversion specification defines how a value in the argument list is formatted and placed into a field at the location of the conversion specification in msg_str. Conversion specifications have this format:
% [[flag] [width] [. precision] [{h | l}]] type
The parameters that can be used in msg_str are:
flag
A code that determines the spacing and justification of the substituted value.
Code | Prefix or justification | Description |
---|---|---|
- (minus) |
Left-justified | Left-justify the argument value within the given field width. |
+ (plus) |
Sign prefix | Preface the argument value with a plus (+ ) or minus (- ) if the value is of a signed type. |
0 (zero) |
Zero padding | Preface the output with zeros until the minimum width is reached. When 0 and the minus sign (- ) appear, 0 is ignored. |
# (number) |
0x prefix for hexadecimal type of x or X |
When used with the o , x , or X format, the number sign (# ) flag prefaces any nonzero value with 0 , 0x , or 0X , respectively. When d , i , or u are prefaced by the number sign (# ) flag, the flag is ignored. |
' ' (blank) |
Space padding | Preface the output value with blank spaces if the value is signed and positive. This padding is ignored when included with the plus sign (+ ) flag. |
width
An integer that defines the minimum width for the field into which the argument value is placed. If the length of the argument value is equal to or longer than width, the value is printed with no padding. If the value is shorter than width, the value is padded to the length specified in width.
An asterisk (*
) means that the width is specified by the associated argument in the argument list, which must be an integer value.
precision
The maximum number of characters taken from the argument value for string values. For example, if a string has five characters and precision is 3, only the first three characters of the string value are used.
For integer values, precision is the minimum number of digits printed.
An asterisk (*
) means that the precision is specified by the associated argument in the argument list, which must be an integer value.
{h | l} type
Used with character types d
, i
, o
, s
, x
, X
, or u
, and creates shortint (h
) or longint (l
) values.
Type specification | Represents |
---|---|
d or i |
Signed integer |
o |
Unsigned octal |
s |
String |
u |
Unsigned integer |
x or X |
Unsigned hexadecimal |
These type specifications are based on the ones originally defined for the printf
function in the C standard library. The type specifications used in RAISERROR
message strings map to Transact-SQL data types, while the specifications used in printf
map to C language data types. Type specifications used in printf
aren't supported by RAISERROR
when Transact-SQL doesn't have a data type similar to the associated C data type. For example, the %p
specification for pointers isn't supported in RAISERROR
because Transact-SQL doesn't have a pointer data type.
To convert a value to the Transact-SQL bigint data type, specify %I64d
.
@local_variable
A variable of any valid character data type that contains a string formatted in the same manner as msg_str. @local_variable must be char or varchar, or be able to be implicitly converted to these data types.
severity
The user-defined severity level associated with this message. When using msg_id to raise a user-defined message created using sp_addmessage
, the severity specified on RAISERROR
overrides the severity specified in sp_addmessage
.
For severity levels from 19 through 25, the WITH LOG
option is required. Severity levels less than 0
are interpreted as 0
. Severity levels greater than 25 are interpreted as 25.
Caution
Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.
You can specify -1
to return the severity value associated with the error, as shown in the following example.
RAISERROR (15600, -1, -1, 'mysp_CreateCustomer');
Here is the result set.
Msg 15600, Level 15, State 1, Line 1
An invalid parameter or option was specified for procedure 'mysp_CreateCustomer'.
state
An integer from 0 through 255. Negative values default to 1. Values larger than 255 shouldn't be used.
If the same user-defined error is raised at multiple locations, using a unique state number for each location can help find which section of code is raising the errors.
argument
The parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be zero or more substitution parameters, but the total number of substitution parameters can't exceed 20. Each substitution parameter can be a local variable or any of these data types: tinyint, smallint, int, char, varchar, nchar, nvarchar, binary, or varbinary. No other data types are supported.
option
A custom option for the error and can be one of the values in the following table.
Value | Description |
---|---|
LOG |
Logs the error in the error log and the application log for the instance of the SQL Server Database Engine. Errors logged in the error log are currently limited to a maximum of 440 bytes. Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG .Applies to: SQL Server |
NOWAIT |
Sends messages immediately to the client. Applies to: SQL Server, Azure SQL Database, and Azure SQL Managed Instance |
SETERROR |
Sets the @@ERROR and ERROR_NUMBER values to msg_id or 50000, regardless of the severity level.Applies to: SQL Server, Azure SQL Database, and Azure SQL Managed Instance |
Remarks
The errors generated by RAISERROR
operate the same as errors generated by the Database Engine code. The values specified by RAISERROR
are reported by the ERROR_LINE
, ERROR_MESSAGE
, ERROR_NUMBER
, ERROR_PROCEDURE
, ERROR_SEVERITY
, ERROR_STATE
, and @@ERROR
system functions. When RAISERROR
is run with a severity of 11 or higher in a TRY
block, it transfers control to the associated CATCH
block. The error is returned to the caller if RAISERROR
is run:
- Outside the scope of any
TRY
block. - With a severity of 10 or lower in a
TRY
block. - With a severity of 20 or higher that terminates the database connection.
CATCH
blocks can use RAISERROR
to rethrow the error that invoked the CATCH
block by using system functions such as ERROR_NUMBER
and ERROR_MESSAGE
to retrieve the original error information. @@ERROR
is set to 0
by default for messages with a severity from 1 through 10.
When msg_id specifies a user-defined message available from the sys.messages
catalog view, RAISERROR
processes the message from the text column using the same rules as are applied to the text of a user-defined message specified using msg_str. The user-defined message text can contain conversion specifications, and RAISERROR
maps argument values into the conversion specifications. Use sp_addmessage
to add user-defined error messages and sp_dropmessage
to delete user-defined error messages.
RAISERROR
can be used as an alternative to PRINT
to return messages to calling applications. RAISERROR
supports character substitution similar to the functionality of the printf
function in the C standard library, while the Transact-SQL PRINT
statement doesn't. The PRINT
statement isn't affected by TRY
blocks, while a RAISERROR
run with a severity of 11 to 19 in a TRY block transfers control to the associated CATCH
block. Specify a severity of 10 or lower to use RAISERROR
to return a message from a TRY
block without invoking the CATCH
block.
Typically, successive arguments replace successive conversion specifications; the first argument replaces the first conversion specification, the second argument replaces the second conversion specification, and so on. For example, in the following RAISERROR
statement, the first argument of N'number'
replaces the first conversion specification of %s
; and the second argument of 5
replaces the second conversion specification of %d.
RAISERROR (N'This is message %s %d.', -- Message text.
10, -- Severity,
1, -- State,
N'number', -- First argument.
5); -- Second argument.
-- The message text returned is: This is message number 5.
GO
If an asterisk (*
) is specified for either the width or precision of a conversion specification, the value to be used for the width or precision is specified as an integer argument value. In this case, one conversion specification can use up to three arguments, one each for the width, precision, and substitution value.
For example, both of the following RAISERROR
statements return the same string. One specifies the width and precision values in the argument list; the other specifies them in the conversion specification.
RAISERROR (N'<\<%*.*s>>', -- Message text.
10, -- Severity,
1, -- State,
7, -- First argument used for width.
3, -- Second argument used for precision.
N'abcde'); -- Third argument supplies the string.
-- The message text returned is: << abc>>.
GO
RAISERROR (N'<\<%7.3s>>', -- Message text.
10, -- Severity,
1, -- State,
N'abcde'); -- First argument supplies the string.
-- The message text returned is: << abc>>.
GO
Permissions
Any user can specify a severity level from 0 through 18. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE
permissions.
Examples
A. Return error information from a CATCH block
The following code example shows how to use RAISERROR
inside a TRY
block to cause execution to jump to the associated CATCH
block. It also shows how to use RAISERROR
to return information about the error that invoked the CATCH
block.
Note
RAISERROR
only generates errors with state from 1 through 127. Because the Database Engine might raise errors with state 0, we recommend that you check the error state returned by ERROR_STATE before passing it as a value to the state parameter of RAISERROR
.
BEGIN TRY
-- RAISERROR with severity 11-19 will cause execution to
-- jump to the CATCH block.
RAISERROR ('Error raised in TRY block.', -- Message text.
16, -- Severity.
1 -- State.
);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
B. Create an ad hoc message in sys.messages
The following example shows how to raise a message stored in the sys.messages
catalog view. The message was added to the sys.messages
catalog view by using the sp_addmessage
system stored procedure as message number 50005
.
EXEC sp_addmessage @msgnum = 50005,
@severity = 10,
@msgtext = N'<\<%7.3s>>';
GO
RAISERROR (50005, -- Message ID.
10, -- Severity,
1, -- State,
N'abcde'); -- First argument supplies the string.
-- The message text returned is: << abc>>.
GO
EXEC sp_dropmessage @msgnum = 50005;
GO
C. Use a local variable to supply the message text
The following code example shows how to use a local variable to supply the message text for a RAISERROR
statement.
DECLARE @StringVariable NVARCHAR(50);
SET @StringVariable = N'<\<%7.3s>>';
RAISERROR (@StringVariable, -- Message text.
10, -- Severity,
1, -- State,
N'abcde'); -- First argument supplies the string.
-- The message text returned is: << abc>>.
GO
Related content
- What are the SQL database functions?
- DECLARE @local_variable (Transact-SQL)
- PRINT (Transact-SQL)
- sp_addmessage (Transact-SQL)
- sp_dropmessage (Transact-SQL)
- sys.messages (Transact-SQL)
- xp_logevent (Transact-SQL)
- @@ERROR (Transact-SQL)
- ERROR_LINE (Transact-SQL)
- ERROR_MESSAGE (Transact-SQL)
- ERROR_NUMBER (Transact-SQL)
- ERROR_PROCEDURE (Transact-SQL)
- ERROR_SEVERITY (Transact-SQL)
- ERROR_STATE (Transact-SQL)
- TRY...CATCH (Transact-SQL)