Scribd
Upload
163 views

SQL Server Statement Overview: Raiseerror

The RAISERROR statement allows you to generate custom error messages in SQL Server and return them in the same format as system errors. It has a syntax that includes specifying a message ID, severity level, and state. You can add custom messages using sp_addmessage and remove them with sp_dropmessage. RAISERROR is used to troubleshoot code, return variable messages, and cause execution to jump from TRY to CATCH blocks.

Uploaded by

mortin
Copyright
© © All Rights Reserved
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
163 views

SQL Server Statement Overview: Raiseerror

The RAISERROR statement allows you to generate custom error messages in SQL Server and return them in the same format as system errors. It has a syntax that includes specifying a message ID, severity level, and state. You can add custom messages using sp_addmessage and remove them with sp_dropmessage. RAISERROR is used to troubleshoot code, return variable messages, and cause execution to jump from TRY to CATCH blocks.

Uploaded by

mortin
Copyright
© © All Rights Reserved
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

SQL Server RAISEERROR statement overview

The RAISERROR 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 statement
allows you to set a specific message id, level of severity, and state for the error messages.
The following illustrates the syntax of the RAISERROR statement:
1 RAISERROR ( { message_id | message_text | @local_variable }  
2     { ,severity ,state }  
3     [ ,argument [ ,...n ] ] )  
4     [ WITH option [ ,...n ] ];
Let’s examine the syntax of the RAISERROR for better understanding.
message_id
The message_id is a user-defined error message number stored in
the sys.messages catalog view.
To add a new user-defined error message number, you use the stored
procedure sp_addmessage. A user-defined error message number should be greater than
50,000. By default, the RAISERROR statement uses the message_id 50,000 for raising an
error.
The following statement adds a custom error message to the sys.messages view:
1 EXEC sp_addmessage
2     @msgnum = 50005,
3     @severity = 1,
4     @msgtext = 'A custom error message';
To verify the insert, you use the following query:

1 SELECT    
2     *
3 FROM    
4     sys.messages
5 WHERE
6     message_id = 50005;
To use this message_id, you execute the RAISEERROR statement as follows:
1 RAISERROR ( 50005,1,1)
Here is the output:

1 A custom error message


2 Msg 50005, Level 1, State 1
To remove a message from the sys.messages, you use the stored
procedure sp_dropmessage. For example, the following statement deletes the message id
50005:
1 EXEC sp_dropmessage
2     @msgnum = 50005;  
message_text
The message_text is a user-defined message with formatting like the printf function in C
standard library. The message_text can be up to 2,047 characters, 3 last characters are
reserved for ellipsis (…). If the message_text contains 2048 or more, it will be truncated
and is padded with an ellipsis.
When you specify the message_text, the RAISERROR statement uses message_id 50000 to
raise the error message.
The following example uses the RAISERROR statement to raise an error with a message text:
1 RAISERROR ( 'Whoops, an error occurred.',1,1)
The output will look like this:

1 Whoops, an error occurred.


2 Msg 50000, Level 1, State 1
severity
The severity level is an integer between 0 and 25, with each level representing the
seriousness of the error.

1 0–10 Informational messages


2 11–18 Errors
3 19–25 Fatal errors
state
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.

WITH option
The option can be LOG, NOWAIT, or SETERROR:

 WITH LOG logs the error in the error log and application log for the instance of the
SQL Server Database Engine.
 WITH NOWAIT sends the error message to the client immediately.
 WITH SETERROR sets the ERROR_NUMBER and @@ERROR values to message_id or
50000, regardless of the severity level.
SQL Server RAISERROR examples
Let’s take some examples of using the RAISERROR statement to get a better understanding.
A) Using SQL Server RAISERROR with TRY CATCH block example
In this example, we use the RAISERROR inside a TRY block to cause execution to jump to the
associated CATCH block. Inside the CATCH block, we use the RAISERROR to return the error
information that invoked the CATCH block.
1 DECLARE
2     @ErrorMessage  NVARCHAR(4000),
3     @ErrorSeverity INT,
4     @ErrorState    INT;
5  
6 BEGIN TRY
7     RAISERROR('Error occurred in the TRY block.', 17, 1);
8 END TRY
9 BEGIN CATCH
10     SELECT
11         @ErrorMessage = ERROR_MESSAGE(),
12         @ErrorSeverity = ERROR_SEVERITY(),
13         @ErrorState = ERROR_STATE();
14  
15     -- return the error inside the CATCH block
16     RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
17 END CATCH;
Here is the output:

1 Msg 50000, Level 17, State 1, Line 16


2 Error occurred in the TRY block.
B) Using SQL Server RAISERROR statement with a dynamic message text
example
The following example shows how to use a local variable to provide the message text for
a RAISERROR statement:
1 DECLARE @MessageText NVARCHAR(100);
2 SET @MessageText = N'Cannot delete the sales order %s';
3  
4 RAISERROR(
5     @MessageText, -- Message text
6     16, -- severity
7     1, -- state
8     N'2001' -- first argument to the message text
9 );
The output is as follows:

1 Msg 50000, Level 16, State 1, Line 5


2 Cannot delete the sales order 2001
When to use RAISERROR statement
You use the RAISERROR statement in the following scenarios:

 Troubleshoot Transact-SQL code.


 Return messages that contain variable text.
 Examine the values of data.
 Cause the execution to jump from a TRY block to the associated CATCH block.
 Return error information from the CATCH block to the callers, either calling batch or
application.

You might also like