SQL Server sp_addmessage System Stored Procedure

About sp_addmessage system stored procedure

The sp_addmessage system stored procedure lets you add error messages to SQL Server that can be referenced in code. This is helpful for standardized error messages that will be used throughout your application, especially if they need to be able to support multiple languages, but not so much for ad-hoc error messages.

Parameters

The sp_addmessage system stored procedure in SQL Server accepts the following six parameters, although not all of them are required.

1. @msgnum parameter.

This is a required value and is the error message number that will be used to call the error message. This must be a whole number greater than 50000. The @msgnum value can be left empty to have an available error message number assigned. When putting error messages into the database in multiple languages the same error number must be used for all the languages which is shown below in Example 1.

Example 1: Entering a sample message in multiple languages

execsp_addmessage@msgnum=50001,@severity=16,@msgtext='Sample Message',@lang='us_english',@with_log='TRUE' GO execsp_addmessage@msgnum=50001,@severity=16,@msgtext='Mensaje de la muestra',@lang='spanish',@with_log='TRUE' GO execsp_addmessage@msgnum=50001,@severity=16,@msgtext='Message témoin',@lang='French',@with_log='TRUE' GO

2. @severity.

This is the severity or priority of the error message. Error messages are defined with specific severities, but any severity can be specified when an error is raised by the RAISERROR statement in T-SQL. The @severity value must be a number between 1 and 25.

3. @msgtext.

The value of the @msgtext parameter must fit within nvarchar(255). When the SQL Server error message will support parameters, there are two ways that error messages are written. The first is for the default language and the second is for language specific error messages. There are five different data types that are supported when defining the default error message. The percent (%) code will define the data type. To use a signed integer use either “d” or “i”, an unsigned octal is “o”, an unsigned integer is “u” and an unsigned hexadecimal is “x” or “X”. To specify a string value specify a value of “s”, this is demonstrated below in Example 2.

Example 2: Error message with two string parameters

execsp_addmessage@msgnum=50002,@severity=16,@msgtext='Error in database %s, in module %s',@lang='us_english',@with_log='TRUE',@replace='REPLACE'

When adding in a language specific error message the parameters of %s are replaced with numeric values to make certain that the correct values are put in the correct place. See Example 3 below.

Example 3: French language version of a parameterized error message

execsp_addmessage@msgnum=50002,@severity=16,@msgtext='Erreurdans la base de données %1, dans le module %2',@lang='French',@with_log='TRUE'

In Example 3, the instances where a user logs into the SQL Server database with a default language of English or French the correct error message will be shown. If someone logs into the SQL Server database using a language not defined in the error message and attempts to raise the error it will display in us_english. Regardless of the default language of the SQL Server instance, errors must always be entered in as us_english first.

4.  @lang.

The @lang specifies the language of the error message. In the sys.syslanguages system catalog view, the language values appear in the name column.

5. @with_log parameter.

This parameter tells the SQL Server when the error is raised if the message should be written to the ERRORLOG and the Windows Application Log. This parameter is varchar(5) and allows only the values of “TRUE” or “FALSE”.

6. @replace parameter.

This parameter tells the SQL Server to replace an existing error message in sys.messages system catalog. This parameter is varchar(7) and allows only a single value which is “REPLACE”. To use the @replace parameter the @msgnum parameter must be specified. When you replace the us_english version of the error message the severity for all other language specific versions will be updated.