Custom error messages


July 24, 2019 by Kenneth Fisher

I’m sure lots of you have used the function RAISERROR to handle an error caused by your code. The problem is, what do you do if the error you want to display isn’t one that Microsoft choose to include in the list of errors? (sys.messages)

You have two options. The first and easiest is to just include the message in the RAISERROR command.

RAISERROR ('The berfmus has been distructumified by the yusnur.',10,1);

But if you are going to have to use this error on a regular basis (the one above being a good example) you might want to add it to the list of possible errors using the function sp_addmessage.

EXEC sp_addmessage 50001, 10, 
     'The berfmus has been distructumified by the yusnur.';

Now if we run an error just using the code 50001 we get the correct message.

RAISERROR (50001,10,1);

This certainly saves us some typing but honestly, I prefer including the message in the error. I don’t see much of a benefit to adding the error to sys.messages and I think it makes the code less readable. Yes you can (and in this case should) include comments, but that kind of takes away the one benefit I can think of to add a message (less typing). So why explain all of this if I don’t think you should use it? To show you this:

RAISERROR (50002,10,1);

Msg 18054, Level 16, State 1, Line 8
Error 50002, severity 10, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

If you start seeing errors like this in your error log it’s because someone decided to use a custom message but forgot to add the message into sys.messages. At that point, it’s probably easier to go ahead and add the messages rather than changing all of the code.

One thought on “Custom error messages

  1. […] Kenneth Fisher shows how you can build your own custom SQL Server error messages: […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,696 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: