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:


@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:

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:


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


Kapil Singh

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

Follow me on Twitter