Creating User Defined Error Message In SQL Server 2008
In this article I demonstrate how to create user defined error message using system stored procedure.
In this article I demonstrate how to create user defined error message using system stored procedure i.e. sp_adddmessage. We use sp_addmessage to add a custom message for your application to use. New error message created is added to the sys.message system catalog view and after that we use a RAISERROR Command that allows to invoke the error message.
Syntax
sp_addmessage [ @msgnum = ] msg_id ,
[@severity = ]severity,
[@msgtext = ]'msg'
[,[@lang = ]'language']
[,[@with_log = ]'with_log']
[,[@replace = ]'replace']
where,
sp_addmessage is system stored procedure which is used to define a user defined custom error message.
msg_id specifies error id which is supplied by user which can be between 50,001 and 2,147,483,647.
severity is the severity level of your message which can be between 1 and 25.
msg is actual error message.
language The language is used if you want to specify any language in which the error message is written.
with_log specifies whether or not the message will be written to the Windows application log when error is invoked.
replace when the same message number already exists but you want to replace it with new id and language, use this parameter.
Example
Creating New Message
USE mcn
GO
EXEC sp_addmessage
400021,
9,
N'This error message is custom error message for an application'
GO
Using New Message with the help of RAISERROR command
raiserror (400021,9,1)
Output:
How it works:
EXEC sp_addmessage
400021,
9,
N'This error message is custom error message for an application'
The first parameter, 400021 was the error message ID. This value can be between 50,001 and 2,147,483,647. The second parameter 9 indicates its severity level and the third parameter was error message. Remaining parameter language, with_log, and replace are not used here.
raiserror (400021,9,1)
RAISERROR is used to return application or business logic related error. In above example first parameter was the error message ID, the second parameter was severity level and third parameter was state which is a number that can be used to identify which part of your code throws an error.