Sp_addmessage – Day 7 – SQL Server System Stored Procedure

Hi friends, today in SQL Server system stored procedure series 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.

Regards,

Kapil Singh

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook 

Follow me on Twitter

Avatar

About Kapil Singh Kumawat

Kapil Singh Kumawat has been working with SQL Server since last 5 years. He is from Jaipur, Rajasthan, India and currently working with Cognizant Technology Solutions as SQL Server Developer. He has good experience in performance tuning, SSIS, data migration and data designing. Apart from database he has interest in travelling, watching football and listening music.

View all posts by Kapil Singh Kumawat →

Leave a Reply

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