RAISERROR()

Introduction

Microsoft Error handling is an important factor for every developer. Here in this article, I am not going to explain the entire process of error handling. But here in this article I am trying to explain most important portion of Error handling is called RaisError() function.  

Syntax of RaisError()

RAISERROR ( { Message ID | Message Text}

            { ,severity ,state }

            [ ,argument [ ,...n ] ] )

            [ WITH option [ ,...n ] ]

Setting Custom Error Messages

The user defines error message number stores on sys.messages catalog view. First have a look on the catalog view.

SELECT * FROM sys.sysmessages;

Understanding the Syntax

Here the Error Number is very important. The user defines error numbers begins at greeter then 50000.

We can add the custom error message by using system stored procedure named sp_AddMessage.

sp_addmessage [ @msgnum= ] msg_id, [ @severity= ] severity ,

              [ @msgtext = ' ] 'message'

              [, [ @lang = ] 'Language' ]

              [, [ @with_log = ] 'log' ]

              [, [ @replace = ] 'replace' ]

@msgnum

It indicate the number of Message for user define message it should be grater then 50000

@severity

It means the priority of the Error Messages. It’s range from 0-25. But for user define message we use 0-19. Severity level 20-25 is considered as fatal error.

 A TRY…CATCH construct catches all execution errors with severity greater than 10 that do not terminate the database connection.

Errors with severity from 0 through 10 are informational messages and do not cause execution to jump from the CATCH block of a TRY…CATCH construct.

Errors that terminate the database connection, usually with severity from 20 through 25, are not handled by the CATCH block because execution is aborted when the connection terminates.

@msggtext

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

We can pass the optional custom parameters within the message text. This parameter is useful to display any custom messages within the message text. The details of the parameters are mentioned bellow.

Type of Separation	Representation
d or i	Signed integer
O	Unsigned Octal
S	String
U	Unsigned Integer

@lang

It means the language that we want to specify.

@with_log

Set TRUE to log the event in event viewer.

@replace

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

Example with sp_AddMessage

Here we are going to add a custom error message by using sp_AddMessage stored procedure.

EXEC sp_addmessage

      @msgnum   = 50009,

      @severity = 1,

      @msgtext  = 'Example Custom Error Message';

GO

SELECT * FROM sys.sysmessages where error=50009;

Here we provide an example to understand it properly.

EXEC sp_addmessage

@msgnum   = 50010,

@severity = 1,

@msgtext  = 'Invalid Sales Order Number : %s for Quantity :%i';

GO

SELECT * FROM sys.sysmessages where error=50010;

   

error	severity	dlevel	description	msglangid
50010	1	0	Invalid Sales Order Number : %s for Quantity :%i	1033

How to Call  RaisError() Function

BEGIN

     DECLARE @str_Sorder  VARCHAR(max),

             @int_Qty     INT;

               

     SET @str_Sorder = 'ORDER-1';

     SET @int_Qty = 30;

       

    RAISERROR(50010, 1, 1, @str_Sorder, @int_Qty);

END

Invalid Sales Order Number : ORDER-1 for Quantity :30

Msg 50010, Level 1, State 1

Hope you like it.

Posted by: MR. JOYDEEP DAS