{"id":162,"date":"2019-09-24T07:59:58","date_gmt":"2019-09-24T07:59:58","guid":{"rendered":"http:\/\/sumitjangid.com\/?p=162"},"modified":"2019-09-24T08:03:46","modified_gmt":"2019-09-24T08:03:46","slug":"raiseerror","status":"publish","type":"post","link":"http:\/\/sumitjangid.com\/index.php\/2019\/09\/24\/raiseerror\/","title":{"rendered":"RaiseError"},"content":{"rendered":"\n

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

SQL Server RAISEERROR<\/code> statement overview<\/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\n
1234<\/td>RAISERROR ( { message_id | message_text | @local_variable }      { ,severity ,state }      [ ,argument [ ,…n ] ] )      [ WITH option [ ,…n ] ];<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Let\u2019s examine the syntax of the RAISERROR<\/code> for better understanding.<\/p>\n\n\n\n

message_id<\/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\n
1234<\/td>EXEC sp_addmessage     @msgnum = 50005,     @severity = 1,     @msgtext = ‘A custom error message’;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

To verify the insert, you use the following query:<\/p>\n\n\n\n
123456<\/td>SELECT        *FROM        sys.messagesWHERE     message_id = 50005;<\/td><\/tr><\/tbody><\/table>\n\n\n\n

To use this message_id, you execute the RAISEERROR<\/code> statement as follows:<\/p>\n\n\n\n
1<\/td>RAISERROR ( 50005,1,1)<\/td><\/tr><\/tbody><\/table>\n\n\n\n

Here is the output:<\/p>\n\n\n\n
12<\/td>A custom error messageMsg 50005, Level 1, State 1<\/td><\/tr><\/tbody><\/table>\n\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\n
12<\/td>EXEC sp_dropmessage     @msgnum = 50005;  <\/td><\/tr><\/tbody><\/table>\n\n\n\n

message_text<\/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 (\u2026). 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\n
1<\/td>RAISERROR ( ‘Whoops, an error occurred.’,1,1)<\/td><\/tr><\/tbody><\/table>\n\n\n\n

The output will look like this:<\/p>\n\n\n\n
12<\/td>Whoops, an error occurred.Msg 50000, Level 1, State 1<\/td><\/tr><\/tbody><\/table>\n\n\n\n

severity<\/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\n
123<\/td>0\u201310 Informational messages11\u201318 Errors19\u201325 Fatal errors<\/td><\/tr><\/tbody><\/table>\n\n\n\n

state<\/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<\/h3>\n\n\n\n

The option can be LOG<\/code>, NOWAIT<\/code>, or SETERROR<\/code>:<\/p>\n\n\n\n