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
Now, we can use this custom message during error handling using RAISERROR command:
RAISERROR(50001,16,1,'Error') -- msgid, severity, state, parameters
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
