User Defined Error Message in SQL Server 2012

Introduction

In this article I describe how to create a User Defined error message in SQL Server 2012. We use SP_addmessage to add a custom message and after that we use a RAISERROR Statement to invoke the custom message.

SP_addmessage:

We use the SP_admessage Stored Procedure to define a User Defined Custom Error Message. This Stored Procedure adds a record to the sys.message system view.
A User Defined message should have a message number of 50000 or higher with a severity of 1 to 25.

Syntax:

sp_addmessage [ @msgnum = ] msg_id ,
[ @severity = ] severity ,
[ @msgtext = ] 'msg'
[ , [ @lang = ] 'language' ]
[ , [ @with_log = ] 'with_log' ]
[ , [ @replace = ] 'replace' ]

Here mcg_id is the id of the message which can be between 50000 and 2147483647. The severity is the level of the message which can be between 1 and 25. For User Defined messages we can use it a value of 0 to 19. The severity level between 20 to 25 can be set by the administrator. Severity levels from 20 through 25 are considered fatal. 

The actual error message is "msg", which uses a data type of nvarchar(255). The maximum characters limit is 2,047. Any more than that will be truncated. The language is used if you want to specify any language. Replace is used when the same message number already exists, but you want to replace the string for that ID, you have to use this parameter.

RAISERROR:

The RAISERROR statement generates an error message by either retrieving the message from the sys.messages catalog view or constructing the message string at runtime. It is used to invoke the the User Defined error message. First we create a User Defined error message using SP_addmessage and after that we invoke that by the use of RAISERROR.

Syntax:

RAISERROR ( { msg_id  }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]

Example:

EXEC sp_addmessage

500021,

10,

'THis error message is created by Deepak Arora'

go

RAISERROR (500021, 10, 1)

Output:



Replacement of Message.

EXEC sp_addmessage

500021,

10,

'Previous error message is replaced by Deepak Arora ',

@lang='us_english',

@with_log='false',

@replace='replace'

GO

RAISERROR (500021, 10, 1)

Output:



Altering the message:

exec sp_altermessage 500021,@parameter='with_log', @parameter_value='true'

Output:



Droping the message:

exec sp_dropmessage 500021

Output:



Summary:
 
In this article I described the User Defined error message in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.