Stored Procedure

Add user-defined message in sys.message table using stored procedure sp_addmessage

Hi friends, today you will learn about how to add user-defined message in sys.messages table using stored procedure sp_addmessage.

Sp_addmessage stored procedure is used to add user-defined error message  which can be viewed using sys.messages table. It requires membership in sysadmin and serveradmin server roles.

Syntax of sp_Addmessage is as follows:

sp_addmessage [ @msgnum= ] msg_id , [ @severity= ] severity , [ @msgtext= ] ‘msg’

[ , [ @lang= ] ‘language’ ]

[ , [ @with_log= ] { ‘TRUE’ | ‘FALSE’ } ]

[ , [ @replace= ] ‘replace’ ]

Arguments

@msgnum is the Id of the message having datatype Int. Msg_id for the user defined message can be range between 50,001 to 2,147,483,647. Msg_id must be unique along with the combination of language.

@severity is the severity level of the error having datatype smallint.  Level of severity can be range between 1 to 25.

@message is the text of error message having datatype nvarchar(255).

@lang is the language of the message which specifies in which language message is written.

@with_log is used to specify whether message to be written to Windows applicaton log or not. Its default value is FALSE having datatype varchar(7).

@replace is used to replace the existing error message having same msg id.

Now, let’s add user-defined message using this procedure:

EXEC sp_addmessage 50001, 16,’Please enter the value in range from 1 to 100′,’us_english’, FALSE, NULL

As we have added a custom message in sys.messages system table, let’s check that newly added entry:

SELECT * FROM sys.messages

sp_addmessage

Now, we can use this custom message during error handling using RAISERROR command:

RAISERROR(50001,16,1,’Error’) — msgid, severity, state, parameters

sp_addmessage2

That’s all folks for the day. Hope you like the post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s