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.