SQL Server RAISERROR Statement with Simple Examples

The SQL RAISERROR statement is used to send a custom message to the client application. It also can be used to debug the application and applies to the error handling mechanism.

SQL RAISERROR Statement Syntax and Parameters

The syntax of the SQL RAISERROR statement is the following:

RAISERROR ( { message_text | message_id | @local_variable }  
    { ,severity ,state }  
    [ ,argument [ ,...n ] ]   
    [ WITH option [ ,...n ] ];

Below you can see the explanation of the RAISERROR keyword parameters that you can specify:

message_text – the message you want to display for an error. Note: We can add custom messages to show the error information. See it explained in the second section of the article.

message_id – the id of the error message.  If you want to display the user-defined message, you must define it. View the list of message_ids in the sys.messages DMV.

Query

select * from sys.messages

The Output:

severity – the severity of an error. The datatype of the severity variable is smallint, and values are between 0 and 25. The valid values of error severity are as follows:

• 0-10 – informational messages
• 11-18 – errors
• 19-25 – fatal errors

Note: If you create a user-defined message, the severity specified in the user-defined message will be overridden by the severity specified in the RAISERROR statement.

state – the unique identification number that you can use to identify the code section that is causing the error. The datatype of the state parameter is smallint, and values are between 0 and 255.

Now, let us proceed to practical examples.

Example 1: Use SQL Server RAISERROR Statement to Print the Output

In this example, you can see how we can display the error or information message using the RAISERROR statement.

Suppose you want to display the message after inserting records into the table. We can use SQL PRINT or RAISERROR statements. Following is the code:

SET nocount ON 

INSERT INTO tblpatients 
            (patient_id, 
             patient_name, 
             address, 
             city) 
VALUES     ('OPD00006', 
            'Nimesh Upadhyay', 
            'AB-14, Ratnedeep Flats', 
            'Mehsana') 

RAISERROR ( 'Patient detail added successfully',1,1) 

The Output:

As you can see in the above image, the message ID is 50000 because it is a user-defined message.

Example 2: SQL RAISERROR Statement with the Dynamic Message Text

Now, see how we can create the dynamic message text for the SQL RAISERROR statement.

Suppose we want to print the patient’s ID in the message. I have defined the local variable named @PatientID, which holds the patient_id.  To display the value of the @patientID variable within the message text, we can use the following code:

DECLARE @PatientID VARCHAR(15) 
DECLARE @message NVARCHAR(max) 

SET @PatientID='OPD00007' 
SET @message ='Patient detail added successfully. The OPDID is %s' 

INSERT INTO tblpatients 
            (patient_id, 
             patient_name, 
             address, 
             city) 
VALUES     ('' + @PatientID + '', 
            'Nimesh Upadhyay', 
            'AB-14, Ratnedeep Flats', 
            'Mehsana') 

RAISERROR ( @message,1,1,@patientID) 

The Output:

To display the string in the RAISERROR statement, we must use the C-style print statements.

As you can see in the above image, to display the parameter in the message text, I have used the %s option that displays the string value of the parameter. If you want to display the integer parameter, you can use the %d option.

Use SQL RAISERROR in TRY..CATCH Block

In this example, we are adding SQL RAISERROR in the TRY block. When we run this code, it executes to the associated CATCH block. In the CATCH block, we will display the details of the error invoked.

BEGIN try 
    RAISERROR ('Error invoked in the TRY code block.',16,1 ); 
END try 

BEGIN catch 
    DECLARE @ErrorMsg NVARCHAR(4000); 
    DECLARE @ErrSeverity INT; 
    DECLARE @ErrState INT; 

    SELECT @ErrorMsg = Error_message(), 
           @ErrSeverity = Error_severity(), 
           @ErrState = Error_state(); 

    RAISERROR (@ErrorMsg,
               @ErrSeverity,
               @ErrState 
    ); 
END catch;

Thus, we have added the RAISERROR statement with the severity between 11 -19. It causes the execution of the CATCH block.

Within the CATCH block, we are showing the information of the original error using the RAISERROR statement.

The Output:

As you can see, the code has returned the information about the original error.

Now, let us understand how we can add a custom message using the sp_addmessage stored procedure.

sp_addmessage Stored Procedure

We can add the custom message by executing the sp_addmessages stored procedure. The syntax is:

EXEC Sp_addmessage 
  @msgnum= 70001, 
  @severity=16, 
  @msgtext='Please enter the numeric value', 
  @lang=NULL, 
  @with_log='TRUE', 
  @replace='Replace'; 

@msgnum: Specify the message number. The data type of parameter is integer. It is a message ID for the user-defined message.

@severity: Specify the severity level of the error. The valid values are between 1 and 25. The data type of the parameter is smallint.

@messagetext: Specify the message that you want to display. The data type of the parameter is nvarchar(255), and the default value is NULL.

@lang: Specify the language that you want to use to display the error message. The default value is NULL.

@with_log: This parameter is used to write the message to the event viewer. The valid values are TRUE and FALSE. If you specify TRUE, the error message will be written to the windows event viewer. If you choose FALSE, the error will not be written to the windows error log.

@replace: If you want to replace the existing error message with a user-defined message and severity level, you can specify the replace in the stored procedure.

Suppose you want to create an error message that returns an invalid quality error. In the INSERT statement, the value of the product_quantity is between 20 and 100. The message should be treated as an error, and the severity is 16.

To create the message, run the following query:

USE master;
go 
EXEC Sp_addmessage 
  70001, 
  16, 
  N'Product Quantity must be between 20 and 100.'; 
go

Once the message has been added, run the below query to view it:

USE master 
go 
SELECT * FROM   sys.messages WHERE  message_id = 70001 

The Output:

How to Use User-defined Error Messages

As I mentioned earlier, we must use message_id in the RAISERROR statement for the user-defined messages.

We have created a message with the ID of 70001. The RAISERROR statement should be as follows:

USE master 
go 
RAISERROR (70001,16,1 ); 
go 

The Output:

The RAISERROR statement has returned the user-defined message.

sp_dropmessage Stored Procedure

The sp_dropmessage stored procedure is used to delete user-defined messages. The syntax is the following:

EXEC Sp_dropmessage @msgnum

In the syntax, @msgnum specifies the ID of the message you want to delete.

Now, we want to delete the message whose ID is 70001. The query is as follows:

EXEC Sp_dropmessage 70001 

Once the message has been deleted, run the following query to view it:

USE master 
go 
SELECT * FROM   sys.messages WHERE  message_id = 70001 

The Output:

As you can see, the message has been deleted.

Summary

Thus, we have explored the RAISERROR statement, its parameters, and demonstrated its usage in practical examples. We have also clarified the usage of the sp_addmessage and sp_dropmessage stored procedures. Hope that this article brought you some useful insights for your working goals.

We are always glad to hear your comments and maybe additional practical tips if you’d like to share them.

Tags: sql errors, statements Last modified: July 24, 2023