MCTS 70-433 sp_addmessage

The following gives a good basic example of the sp_addmessage system stored procedure, it also covers the basics of severity levels:

Question: How can I add a user-defined error message?

Answer: You can use the sp_addmessage system stored procedure to add a user-defined error message.

You can specify the id of the message (value greater than 50000), severity level from 0 through 25 (to add a message with a severity level from 19 through 25, you must be a member of the sysadmin fixe server role) and the message text (up to 255 characters).

By the way, only members of the sysadmin and serveradmin fixed server roles can execute the sp_addmessage system stored procedure.

This is the example:

USE master EXEC sp_addmessage @msgnum = 50001, @severity = 18, @msgtext = 'Custom error message'

See SQL Server Books Online for more details about the sp_addmessage system stored procedure.

http://www.mssqlcity.com/FAQ/Admin/add_user_defined_error_message.htm