Custom Error Messages
You can read it from…
Error handling
In this article I am trying to illustrate the point related to custom error messages.
Custom error messages allow us to design more business specific error messages. Each custom error message has a severity assignment, which determines how important the error is and identifies how it should be handled. Some error messages are simply informational and are not even captured by error handling. Other error messages are very severe and immediately kill the process on which the statement was executed.
Defining custom error messages
To define a custom error message in SQL Server 2005, we can use the stored procedure namedsp_addmessage, which adds a record to the sys.messages system view.
The syntax is mentioned bellow
sp_addmessage [ @msgnum = ] msg_id ,
[ @severity = ] severity ,
[ @msgtext = ] 'msg'
[ , [ @lang = ] 'language' ]
[ , [ @with_log = ] { 'TRUE' | 'FALSE' } ]
[ , [ @replace = ] 'replace' ]
Arguments
[ @msgnum = ] msg_id
Is the ID of the message. msg_id is int with a default of NULL. msg_id for user-defined error messages can be an integer between 50,001 and 2,147,483,647.
[ @severity = ] severity
Is the severity level of the error. severity is smallint with a default of NULL. Valid levels are from 1 through 25.
[ @msgtext = ] 'msg'
Is the text of the error message. msg is nvarchar(255) with a default of NULL.
[ @lang = ] 'language'
Is the language for this message. language is sysname with a default of NULL. Because multiple languages can be installed on the same server, language specifies the language in which each message is written. When language is omitted, the language is the default language for the session.
[ @with_log = ] { 'TRUE' | 'FALSE' }
Is whether the message is to be written to the Windows application log when it occurs. @with_log is varchar(5) with a default of FALSE. If TRUE, the error is always written to the Windows application log. If FALSE, the error is not always written to the Windows application log but can be written, depending on how the error was raised. Only members of the sysadmin server role can use this option.
[ @replace = ] 'replace'
If specified as the string replace, an existing error message is overwritten with new message text and severity level. replace is varchar(7) with a default of NULL. This option must be specified if msg_id already exists. If you replace a U.S. English message, the severity level is replaced for all messages in all other languages that have the same msg_id.
To execute this stored procedure, we need to provide an error number which will start from 50,001 to 2,147,483,647 for user-defined messages.
Example
Sample-1
USE master
GO
EXEC sp_addmessage
50001,
1,
N'This message is is the Sample Error Message';
Sample-2
EXEC sp_addmessage
50002,
16,
N'This actually causes an error, and is caught by error-handling';
Sample-3
EXEC sp_addmessage
50003,
20,
N'This causes an error, and stops any further processing. This is not caught by error handling.';
Calling Custom Error Messages
Now that my custom error messages are defined, we can use them inside our database engine. To invoke these errors, we’ll use the RAISERROR T-SQL construct.
RAISERROR accepts an error number, a severity level, and a state number.
Example
Sample-1
BEGIN
BEGIN TRY
RAISERROR (50001,1,1) WITH LOG
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE(), ERROR_NUMBER ()
END CATCH
END
Result set:
This message is is the Sample Error Message
Msg 50001, Level 1, State 1
Sample-2
This statement invokes the second custom error message I define above. This message has a defined severity of 16, which will get caught by my CATCH statement. These types of error messages are some of the more commonly seen messages inside the SQL Server database engine.
BEGIN
BEGIN TRY
RAISERROR (50002,16,1) WITH LOG
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE(), ERROR_NUMBER ()
END CATCH
END
Result set:
This actually causes an error, and is caughtby error-handling
|
50002
|
Sample-3
This final snippet calls the third custom message defined above. Due to the severity level defined in this custom error, the CATCH block is not invoked; in fact, the statement and connection is immediately terminated.
BEGIN
BEGIN TRY
RAISERROR (50003, 20,1) WITH LOG
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE(), ERROR_NUMBER ()
END CATCH
END
Result set:
Msg 2745, Level 16, State 2, Line 3
Process ID 52 has raised user error 50003, severity 20. SQL Server is terminating this process.
Msg 50003, Level 20, State 1, Line 3
This causes an error, and stops any further processing. This is not caught by error handling.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
To Alter Error Message
Alters the state of user-defined messages in an instance of the SQL Server Database Engine. User-defined messages can be viewed using the sys.messages catalog view.
Syntax
sp_altermessage [ @message_id = ] message_number,
[ @parameter = ] 'write_to_log',
[ @parameter_value = ] 'value'
Arguments
[ @message_id = ] message_number
Is the error number of the message to alter from sys.messages. message_number is int with no default value.
[ @parameter = ] 'write_to_log'
Is used with @parameter_value to indicate that the message is to be written to the Microsoft Windows application log. write_to_log is sysname with no default value.write_to_log must be set to WITH_LOG or NULL. If write_to_log is set to WITH_LOG or NULL, and the value for @parameter_value is true, the message is written to the Windows application log. If write_to_log is set to WITH_LOG or NULL and the value for @parameter_value is false, the message is not always written to the Windows application log, but may be written depending upon how the error was raised. If write_to_log is specified, the value for @parameter_value must also be specified.
[ @parameter_value = ] 'value'
Is used with @parameter to indicate that the error is to be written to the Microsoft Windows application log. value is varchar(5), with no default value. If true, the error is always written to the Windows application log. If false, the error is not always written to the Windows application log, but may be written depending upon how the error was raised. If value is specified, write_to_log for @parameter must also be specified
Returns
It returns 0 (Success) or 1 (Failure)
Example
sp_altermessage 55001, 'WITH_LOG', 'true';
GO
To DROP a specified Error Message
Drops a specified user-defined error message from an instance of the SQL Server Database Engine. User-defined messages can be viewed using the sys.messages catalog view.
Syntax:
sp_dropmessage [ @msgnum = ] message_number
[ , [ @lang = ] 'language' ]
Arguments
[ @msgnum = ] message_number
Is the message number to drop. message_number must be a user-defined message that has a message number greater than 50000. message_number is int, with a default of NULL.
[ @lang = ] 'language'
Is the language of the message to drop. If all is specified, all language versions of message_number are dropped. language is sysname, with a default of NULL.
Example:
USE master;
GO
EXEC sp_dropmessage 50001;
Hope you like it.
