{"id":1904,"date":"2019-05-01T11:26:26","date_gmt":"2019-05-01T04:26:26","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1904"},"modified":"2020-04-11T20:12:14","modified_gmt":"2020-04-11T13:12:14","slug":"sql-server-raiserror","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-stored-procedures\/sql-server-raiserror\/","title":{"rendered":"SQL Server RAISERROR"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server RAISERROR<\/code> statement to generate user-defined error messages.<\/p>\n\n\n\n

If you develop a new application, you should use the THROW<\/a><\/code> statement instead.<\/p>\n\n\n\n

SQL Server RAISEERROR statement overview #<\/a><\/h2>\n\n\n\n

The RAISERROR<\/code> statement allows you to generate your own error messages and return these messages back to the application using the same format as a system error or warning message generated by SQL Server Database Engine. In addition, the RAISERROR<\/code> statement allows you to set a specific message id, level of severity, and state for the error messages.<\/p>\n\n\n\n

The following illustrates the syntax of the RAISERROR<\/code> statement:<\/p>\n\n\n

RAISERROR ( { message_id | message_text | @local_variable }  \n    { ,severity ,state }  \n    [ ,argument [ ,...n ] ] )  \n    [ WITH<\/span> option<\/span> [ ,...n ] ];\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Let’s examine the syntax of the RAISERROR<\/code> for better understanding.<\/p>\n\n\n\n
message_id #<\/a><\/h3>\n\n\n\n
The message_id<\/code> is a user-defined error message number stored in the sys.messages<\/code> catalog view.<\/p>\n\n\n\n
To add a new user-defined error message number, you use the stored procedure<\/a> sp_addmessage<\/code>. A user-defined error message number should be greater than 50,000. By default, the RAISERROR<\/code> statement uses the message_id<\/code> 50,000 for raising an error.<\/p>\n\n\n\n
The following statement adds a custom error message to the sys.messages<\/code> view:<\/p>\n\n\n
EXEC sp_addmessage \n    @msgnum = 50005, \n    @severity = 1, \n    @msgtext = 'A custom error message';\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To verify the insert, you use the following query:<\/p>\n\n\n
SELECT<\/span>    \n    *\nFROM<\/span>    \n    sys.messages\nWHERE<\/span> \n    message_id = 50005<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To use this message_id, you execute the RAISEERROR<\/code> statement as follows:<\/p>\n\n\n
RAISERROR ( 50005,1,1)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
A custom error message\nMsg 50005, Level 1, State 1\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To remove a message from the sys.messages<\/code>, you use the stored procedure sp_dropmessage<\/code>. For example, the following statement deletes the message id 50005:<\/p>\n\n\n
EXEC sp_dropmessage \n    @msgnum = 50005;  \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
message_text #<\/a><\/h3>\n\n\n\n
The message_text<\/code> is a user-defined message with formatting like the printf<\/code> function in C standard library. The message_text<\/code> can be up to 2,047 characters, 3 last characters are reserved for ellipsis (…). If the message_text<\/code> contains 2048 or more, it will be truncated and is padded with an ellipsis.<\/p>\n\n\n\n
When you specify the message_text<\/code>, the RAISERROR<\/code> statement uses message_id 50000 to raise the error message.<\/p>\n\n\n\n
The following example uses the RAISERROR<\/code> statement to raise an error with a message text:<\/p>\n\n\n
RAISERROR ( 'Whoops, an error occurred.',1,1)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output will look like this:<\/p>\n\n\n
Whoops, an error occurred.\nMsg 50000, Level 1, State 1\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
severity #<\/a><\/h3>\n\n\n\n
The severity level is an integer between 0 and 25, with each level representing the seriousness of the error.<\/p>\n\n\n
0\u201310 Informational messages\n11\u201318 Errors\n19\u201325 Fatal errors\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
state #<\/a><\/h3>\n\n\n\n
The state is an integer from 0 through 255. If you raise the same user-defined error at multiple locations, you can use a unique state number for each location to make it easier to find which section of the code is causing the errors. For most implementations, you can use 1.<\/p>\n\n\n\n
WITH option #<\/a><\/h3>\n\n\n\n
The option can be LOG<\/code>, NOWAIT<\/code>, or SETERROR<\/code>:<\/p>\n\n\n\n