Thursday, 2 October 2014



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' ]

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

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.

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
d or i
Signed integer
Unsigned Octal
Unsigned Integer

It means the language that we want to specify.

Set TRUE to log the event in event viewer.

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';

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';

SELECT * FROM sys.sysmessages where error=50010;

Invalid Sales Order Number : %s for Quantity :%i

How to Call  RaisError() Function

     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);

Invalid Sales Order Number : ORDER-1 for Quantity :30
Msg 50010, Level 1, State 1

Hope you like it.

Posted by: MR. JOYDEEP DAS

No comments:

Post a Comment