in the earlier post we learned about error messages in SQL Server

https://sqljunkieshare.com/2012/03/22/implimenting-error-handling-in-sql-server-day-1/

In this post we will look at how to Implement and Manage User Defined errors

as we learned in the first post that it is very important for any organization to Implement User defined error messages in any business logic  or for any front end application as some times user may not understand the cryptic system error messages

We can solve this problem by adding and managing our own error messages to the SYS.MESSAGES Catalog using a

system stored procedures  SP_ADDMESSAGE, SP_ALTERMESSAGE, SP_DROPMESSAGE

To add User defined error message to SYS.MESSAGES , we use the SP_ADDMESSAGE

sp_addmessage [ @msgnum = ] msg_id

,[ @severity = ] severity

,[ @msgtext = ] ‘msg’
[, [ @lang = ] ‘language’ ]
[, [ @with_log = ] { ‘TRUE’ | ‘FALSE’ } ]
[, [ @replace = ] ‘replace’ ]

SP_ADDMESSAGE has six input parameters

@msgnum is the Message_id (int) column in SYS.MESSAGES , @msgnum can be any number between 50,001 to 2,147,483,647

1 to 50,000 are reserved for system error messages

@severity is the level of severity scope of the error message , 1 to 25

@msgtxt is a NVARCHAR(255)  text column and it supports arguments , we will talk about arguments later in this post

the above three parameters are mandatory to add a message

Message_id and langid in the sys.messages form a composite key , so there can be entries in SYS.MESSAFES

with same messageid’s in different langauges

@lang is the type of language the message will be in , sys.syslanguages has the list of languages the sql server supports , SQL Server supports 32

different languages.

@with_log this parameter accepts either ‘TRUE’ or ‘FALSE’

For a user to specify this parameter user should be a member of SYSADMIN role

There two logs that we will deal with is one windows application log and the second one is database engine log

When this parameter is set to true the error is written to windows application log and database engine log

When this parameter is set to false the error is written to only database engine log

@replace  is used to alter the existing messages

we can Severity , Message text and With log option of error messages

SP_ALTERMESSAGE

CODE:

— To add a UDF error message to sys.messages
–@Msgnum : range 50,001 to 2,147,483,647
–@Severity: 1 to 25
–@Msgtext : NVARCHAR(255) , accpets arguments
–[optional]
–@with_log : TRUE/FALSE
–@lang     : by default it’s the sessions default lanuguage or language in sys.syslangusges
–@replace  : is used to alter the messages, we can alter Severity,Message text,With_log
–columns

sp_addmessage @msgnum =70000, @severity = 24, @msgtext = ‘test error log’

— Using @replace option we can update Severity
— ,with_log and message text

sp_addmessage @msgnum = 70000, @severity = 20
, @msgtext = ‘updated error log’,@with_log =’true’, @replace =’replace’

Select * from sys.messages where message_id = 70000
— SP_ALTERMESSAGE is used only to update the WITH_lOG option
— Access to only members of SYSADMIN roles

sp_altermessage 70000,’WITH_LOG’,’FALSE’
Select * from sys.messages where message_id = 70000

sp_dropmessage @msgnum = 70000 , @lang = ‘us_english’

–@lang is optional , if a message with different languages should be
— droped then ALL is specified in the lang parameter