Home » Using Raise Error in SQL Server Error Handling

Using Raise Error in SQL Server Error Handling

  • by

When developing codes in SQL Server using Tsql, one has to use error handling mechanism, SQL Server has its own Error Handling mechanism where @@Error is used to trap the errors and we can get the Error Message for that error.
RAISERROR allows developers to produce our own error message when running our Query or Stored procedure. RAISERROR is very useful in returning messages back to applications using the same format as a system error or warning message generated by the SQL Server Database Engine. We can also set our own severity for each and every individual message.

RaiseError can be used for raiserror severity level.

sql raiserror vs throw – Raiseerror is better that throw

sql raiserror custom message can be raised with Raiseerror.

sql server raiserror stop execution can be used for stopping execution after encountering the error.

Incorrect syntax near raiseerror error occurs upon syntax issue with raiseerror command.

raiserror with log and sql server error severity level showing is possible.

Syntax:

RAISERROR ( { Message ID | Message Text}
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
Message ID, Message Text

Is a user-defined error message number stored in the sys.messages catalog view. Error numbers for user-defined error messages should be greater than 50000. By default, RAISERROR raises an error message with an error number of 50000.
We can add error number using sp_addmessge statement as shown below:


exec sp_addmessage @msgnum=50010,@severity=1,@msgtext='This is new Demo Error Message'

To check all the error messages use query below:

select * from sys.messages

Testing the Error by running query below:

RAISERROR ( 50010,1,1)

Results:

This is new Demo Error MessageCustom Error Message
Msg 50010, Level 1, State 1


The error message can have a maximum of 2,047 characters and if it exceeds then it will show only 2,044 characters with an ellipsis to indicate that the message has been truncated.


Severity, States

Severity level is mentioned while adding the message using sp_addmessage. Range of Severity level is 0-25. But for user defined message, we have to set it up to 0-19.
20-25 can only be set by the administrator and are considered fatal.
The default State value is 1. The range for state is from 1 to 127. But for most implementations, we use 1. We can use it to indicate which error was thrown by providing a different state for each RAISERROR function in our stored procedure. This is a mandatory parameter while using RAISERROR.

There many other important options in Sp_addmessage which can be set like
@with_log
It will place the error in windows Error log. We have to mention this parameter while adding the message using sp_addmessage.

Example:

exec sp_addmessage @msgnum=50051,@severity=1,_
@msgtext='Demo EVENT VIEWER',@with_log='true'

@lang
Used if you want to specify any language.

@replace
If the same message number already exists, but you want to replace the message text for that ID, you have to use this parameter.

Use Try-Catch with RAISE ERROR
A simple example of RAISERROR inside TRY-CATCH Block for predefined Error Message:

BEGIN TRY
DECLARE @Intval int;
SET @Intval = 1/0; -- Divide by Zero Error
END TRY
BEGIN CATCH
RAISERROR (50012, 1, 1); -- where 50012 is our pre-defined error in sp_addmessage

Leave a Reply

Your email address will not be published. Required fields are marked *