How to Raise Errors Explicitly in SQL Server

How To Raise Errors Explicitly in SQL Server

In this article, I will discuss How to raise errors explicitly in SQL Server with some examples along with we will also discuss the different options that we can use with Raiserror in SQL Server.

How to raise errors explicitly in SQL Server?

Generally, errors raised in a program on predefined reasons like dividing a number by zero, violation of primary key, violation of check, violation of referential integrity etc. but if we want then we can also raise an error in our programs in two different ways.

  1. Raiserror statement
  2. throw statement (new feature of SQL Server 2012)

Syntax: Raiserror

Raiserror (errorid/errormsg, SEVERITY, state)[with log]

Syntax: throw

Throw errorid, errormsg, state

Example1: Write a Procedure for dividing two numbers and raise an error in the program if the division is 1 by using the RAISERROR statement.
CREATE PROCEDURE spDivideBy1(@No1 INT, @No2 INT)
AS
BEGIN
  DECLARE @Result INT
  SET @Result = 0
  BEGIN TRY
    IF @No2 = 1
    RAISERROR ('DIVISOR CANNOT BE ONE', 16, 1)
    SET @Result = @No1 / @No2
    PRINT 'THE RESULT IS: '+CAST(@Result AS VARCHAR)
  END TRY
  BEGIN CATCH
    PRINT ERROR_NUMBER()
    PRINT ERROR_MESSAGE()
    PRINT ERROR_SEVERITY()
    PRINT ERROR_STATE()
  END CATCH
END

Example of execution:

EXEC spDivideBy1 10, 1

Output:

How to raise errors explicitly in SQL Server

The above procedure can also be rewritten with the help of a throw statement in place of Raiserror as following.

ALTER PROCEDURE spDivideBy2(@No1 INT, @No2 INT)
AS
BEGIN
  DECLARE @Result INT
  SET @Result = 0
  BEGIN TRY
    IF @No2 = 1
    THROW 50001,'DIVISOR CANNOT BE ONE', 1
    SET @Result = @No1 / @No2
    PRINT 'THE RESULT IS: '+CAST(@Result AS VARCHAR)
  END TRY
  BEGIN CATCH
    PRINT ERROR_NUMBER()
    PRINT ERROR_MESSAGE()
    PRINT ERROR_SEVERITY()
    PRINT ERROR_STATE()
  END CATCH
END

FOR EXECUTION:

EXEC spDivideBy2 10, 1

OUTPUT:

How to raise errors explicitly in SQL Server

What is the difference between RAISERROR function and throw statement?

If we use any of the two statements in a program for raising an error without try and catch blocks, RAISERROR statement after raising the error will still continue the execution of the program whereas throw statement will terminate the program abnormally on that line. But if they are used under try block both will behave in the same way that is will jump directly to catch block from where the error got raised.

The RAISERROR statement will give an option of specifying the SEVERITY of the error message whereas we don’t have this option in case of throw statement where all error messages will have a default SEVERITY of 16.

In case of RAISERROR, there is a chance of recording the error message into the server log file by using the with log option whereas we cannot do this in case of throw statement. 

In case of throw, we need to specify both the errorid and errormessage to raise the error whereas in case of RAISERROR we can specify either id or message. If the id is not specified default error id is 50000 but if we want to specify only error id first we need to add the error message in the sysmessage table by specifying a unique id to the table.

ADDITIONAL OPTIONS WITH RAISERROR STATEMENT:

With Log:

By using this option in the RAISERROR statement we can record the error message in the SQL Server log file so that if the errors are fatal database administrator can take care of fixing those errors.

NOTE: if the severity of the error is greater than 20 specifying the With Log option is mandatory.

To test this ALTER the procedure spDivideBy1 by changing the raiserror statement as following

RAISERROR (‘DIVISOR CANNOT BE ONE’, 16, 1)WITH LOG

Below is the complete procedure

ALTER PROCEDURE spDivideBy1(@No1 INT, @No2 INT)
AS
BEGIN
  DECLARE @Result INT
  SET @Result = 0
  BEGIN TRY
    IF @No2 = 1
    RAISERROR ('DIVISOR CANNOT BE ONE', 16, 1) WITH LOG
    SET @Result = @No1 / @No2
    PRINT 'THE RESULT IS: '+CAST(@Result AS VARCHAR)
  END TRY
  BEGIN CATCH
    PRINT ERROR_NUMBER()
    PRINT ERROR_MESSAGE()
    PRINT ERROR_SEVERITY()
    PRINT ERROR_STATE()
  END CATCH
END

Now execute the procedure and whenever the given error raises we can watch the error messages recorded under SQL Server log file. To view the log file

Object explorer Go to management node SQL Server log node open the current log file by double clicking on it as shown below.

How to raise errors explicitly in SQL Server

Using substitutional parameters in the error message of RAISERROR:

Just like C language, we can also substitute values into the error message to make the error message as dynamic as following

RAISERROR (‘THE NUMBER %d CANNOT BE DIVIDED BY %d’,16, 1, @No1, @No2)WITH LOG

Raising Errors By storing the Error message in SysMessage table:

We can raise an error without giving the error message in RAISERROR statement but in place of error message we need the specify the error id and to specify the error id we need to record that error id with the error message in SysMessage table by using the system defined procedure “SP_ADDMESSAGE”.

Syntax:

SP_ADDMESSAGE <error id>, <severity>, <error message>

To test this first add a record to sysmessage table as following

EXEC sp_Addmessage 51000, 16, ‘DIVIDE BY ONE ERROR ENCOUNTERED’

Now alter the procedure by changing the RAISERROR statement as following

RAISERROR (51000,16, 1)WITH LOG

Deleting the error messages from sysmessages table:

Syntax:

SP_DROPMESSAGE <error id>

Example:

EXEC sp_dropMessage 51000

In the next article, I will discuss the try catch implementation in SQL Server to handle the error.

SUMMARY

In this article, I try to explain How to raise errors explicitly in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

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