How to add an user-defined system error message.

Problem: How to add a custom message in the system error message when an error is raised.

Solution: sp_addmessage

System Error messages are stored inside master database in sys.messages catalog view.

this system procedure can be used to add a user-defined error message in SQL Server Database.

Parameters required for this to procedure are

1. msg_id(message id should be higher than,50001- 2,147,483,647)
2. severity (error severity level should be specified, 1-25)
3. msg_text(description to show when an error is raised.)
4. @lang(language to show the message)
•  To have a message set in languages other than English, first the English version of message should exist and same severity.
5. @with_log (windows application log, true or false default is false)
6. @replace (allows to replace the message text.)

To drop a message sp_dropmessage can be used.

Example:

In this article I am going to show you how we can add a custom error to system messages.

Example 1:

Let’s add a message to validate negative numbers, to demonstrate the usage I am going to raise an error with message_id we have created using try/catch exceptional handling.

USE master; 

GO 

-- add message.

EXEC sp_addmessage 50001, 16,   N'Values are negative.'

 -- ,@replace= 'REPLACE'  use this if you would like to replace.   ; GO 

DECLARE @value int=-123

BEGIN TRY

IF @value<0

BEGIN

RAISERROR(50001,16,1)

END

END TRY

BEGIN CATCH

RAISERROR(50001,16,1)

END CATCH

OUTPUT :

Msg 50001, Level 16, State 1, Line 19

negative input values are not allowed

  

Example 2:

In this example I would like to add an error_message with different language and raise an error message in French.

To add a message in French, we should add first the message in English. Then set the language to French before raising the message.

USE master; 

GO 

-- add message.

EXEC sp_addmessage 50001, 16,   N'negative input values are not allowed';

EXEC sp_addmessage 50001, 16,   N'Les valeurs d''entrée négatives ne sont pas autorisés',

@lang='French',@replace= 'REPLACE'--  use this if you would like to replace.

 ;  GO 

--  after adding the messages you can verify whether these messages got saved.

SELECT * FROM sys.messages WHERE message_id = 50001

message_id	language_id	severity	is_event_logged	text
50001	1033	16	0	negative input values are not allowed
50001	1036	16	0	Les valeurs d'entrée négatives ne sont pas autorisés

set LANGUAGE N'French'

DECLARE @value int=-123

BEGIN TRY

IF @value<0

BEGIN

RAISERROR(50001,16,1)

END

END TRY

BEGIN CATCH

RAISERROR(50001,16,1)

END CATCH

OUTPUT :

 Msg 50001, Level 16, State 1, Line 19

Les valeurs d'entrée négatives ne sont pas autorisés