Define custom error messages in SQL Server 2005

By defining custom error messages in SQL Server 2005, you can use the severity of these messages to illicit certain types of behavior from the database engine. Tim Chapman provides ideas on how to use custom error messages in your shop.

SQL Server 2005 offers a robust set of tools for handling errors. For instance, the TRY...CATCH construct gives you access to much more detailed error information than you could get in previous versions of SQL Server. If you want to add even more flexibility to your toolkit, I suggest using custom error messages.

Custom error messages allow you to design: business-specific messages, the routines to handle these scenarios, and the advanced logging techniques for error review. 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, you can use the stored procedure sp_addmessage, which adds a record to the sys.messages system view. To execute this stored procedure, you need to provide an error number (which will start above 50000 for user-defined messages), a severity level, and the error message. (MSDN provides a detailed explanation of SQL Server error severity levels.)

Below is a sample code snippet that defines three types of custom error messages. The first one has a severity level of 1, which means it is an informational message and not really an error. The message will print to the screen, but it will not get caught by any error handling procedure. The second custom error has a severity level of 16, which means it is an error that the user can correct. These types of errors are caught by the TRY...CATCH construct in SQL Server 2005. An example of a common level 16 error is division by zero. The third message indicates a system problem has occurred, and the execution of the batch is stopped. You should use these types of messages sparingly, as they are not invoked by any type of error handling, and all previous work is disregarded, rolled back, and the connection ended.

USE master


EXEC sp_addmessage 50001, 1, N'This message is not that big of a deal. This is not caught by error handling, and prints this message to the screen.';
EXEC sp_addmessage 50002, 16, N'This actually causes an error, and is caught by error-handling';
EXEC sp_addmessage 50003, 20, N'This causes an error, and stops any further processing.  This is not caught by error handling.';

Using custom error messages

Now that my custom error messages are defined, I can use them inside my database engine. To invoke these errors, I'll use the RAISERROR TSQL construct. RAISERROR accepts an error number, a severity level, and a state number.

The following snippet uses RAISERROR inside of a TRY...CATCH construct. I am including the WITH LOG option of the RAISERROR statement to write the error message to the application log so that I can review it later if necessary. (This particular error does not invoke the CATCH block due to the severity of the error.)

            RAISERROR  (50001,1,1) WITH LOG





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.


            RAISERROR  (50002,16,1) WITH LOG





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.


            RAISERROR  (50003, 20,1) WITH LOG





After I run the above statement, I receive the following error:

Msg 2745, Level 16, State 2, Line 2

Process ID 51 has raised user error 50003, severity 20. SQL Server is terminating this process.

Msg 50003, Level 20, State 1, Line 2

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.

The error is marked as so severe that if I were to run the same statement again, I receive the following error:

Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

This error states that the connection has been terminated.

Understanding when to use custom error messages

Are custom error messages a clear alternative to using your own custom code to handle business situations? Certainly not. Custom error messages are most useful in terms of having a centralized method for handling different types of business scenarios. You should use custom error messages with such a high severity level sparingly because they kill your connection to the database server.

Additional SQL Server 2005 resources

Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at


TechRepublic's Servers and Storage newsletter, delivered on Monday and Wednesday, offers tips that will help you manage and optimize your data center. Automatically sign up today!