Error Handling - Using Raiserror Alongside Try Catch Throw

Question

I've been playing with the Try-Catch in T-SQL and one thing I've noticed is that there seems to be some misunderstanding / argument in places about the Raiserror function.

The MSDN page on Raiserror states that it is depreciated and new developments should use Try Catch Throw instead, but I've seen a few threads and blogs around that say this is an error and it is in fact an older version of Raiserror that is depreciated, and there is a 'newer' one in existence which is still valid.

The code I've been writing makes use of Raiserror for custom errors within Try blocks and are picked up and handled by the Catch Blocks perfectly fine, but considering that the MSDN page states it is depreciated, I wonder if I'll be able to get away with doing it this way for this project.

The main thing I noticed when playing with Try-Catch was when I want to raise a custom error without using Raiserror, I have to use Throw and repeat the error message in every instance of where that error can occur, meaning if I need to reword an error I have to find every instance of it appearing in all my functions and SPs and make the changes.

Raiserror however allows me to save the error details in the server (using sp_addmessage) and then just call the custom error number, any rewording will then affect every instance where that error can occur, current and future.

Although I do appreciate that there are some occassions when you may want to have slightly different wording on some instances on an error, overall I think the messages would mostly be the same.

What I'm wondering is, does anyone else use Raiserror in this way or do most people stick to just using Throw for custom errors?

The reason I ask is I feel that Raiserror offers a wonderful piece of functionality that Throw alone doesn't seem to offer an adequate replacement for.

Thanks.

Answer 1

RAISERROR in the form

  RAISERROR ( { msg_id | msg_str | @local_variable }
      { ,severity ,state }
      [ ,argument [ ,...n ] ] )
      [ WITH option [ ,...n ] ]

is not deprecated to my knowning. (And Microsoft would only dare to deprecate it. I've been slamming them quite a bit for this idiotic addition of THROW.) There is however, an older syntax of RAISERROR that I don't even remember, which has been deprecated for a long time and which have been removed in SQL 2012.

I am not wholly enthusiastic over custom-error messages added with sp_addmessage. There are a couple of problems with them:

1) It's just a number, so typos can cause confusion.
2) They are a server-wide resource, which means that there can be collisions between databases.
3) Things get difficult if your application is moved from one server to another.

We use custom-errors in the system I work with, but it's only legacy. We wrote our own error-handling procedure which accepts a code which can be looked up in an application table and parameterised.

THROW is handy if you want to re-raise an error, but I strongly recommend against it for raising error messages. If there is no CATCH handler, TRHOW aborts the current batch, without rolling back the existing transaction. A behaviour that was unheard of before SQL 2012. This can have some very nasty effects with orphanded transactions.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Well, I've gone back to the MSDN page http://msdn.microsoft.com/en-us/library/ms178592.aspx and although it doesn't use the word 'depreciated' it does say 'New applications should use THROW instead.', so therein is the confusion.

I think your point 1 comes under testing, any typos can cause problems in code, and testing errors is something I always do.

Points 2 and 3 go hand in hand there, and I can see the potential issues with that. Since any custom messages are serverwide they won't be associated with the DB, so migrating a DB may not migrate the messages as well, correct?

I also appreciate what you're saying with THROW potentially leaving orphaned transactions, while it is a bit of a headache  to always remember to write code in the Catch block to rollback any incomplete transactions, my view is a good programmer would look for that sort of thing.

What I have intending is having the Catch block handle any errors server side, but to also use THROW to send a message to the FE, giving it information about what went wrong and therefore opening the opportunity to let the FE correct things on that side as well. Obviously this should never be done without first cleaning up what procedure started.

While I imagine not every DB developer out there would do things that way, for the case of my project this is a very useful thing to have.

Thinking about what you say with the custom error system, I think I may go a similar route. Although I'm not sure what you mean by 'it's only legacy'.

Perhaps if MS would add a error/message system similar to sp_addmessage and Raiserror but separated per database.

Thanks for the comments.

Answer 3

Points 2 and 3 go hand in hand there, and I can see the potential issues with that. Since any custom messages are serverwide they won't be associated with the DB, so migrating a DB may not migrate the messages as well, correct?

Yes, you would have to migrate those messages separately.

Thinking about what you say with the custom error system, I think I may go a similar route. Although I'm not sure what you mean by 'it's only legacy'.

In our system we can raise an error like this:

   EXEC ael_message_sp @@procid, 16, 'errSomethingwrong'
                       'Yes, you did it wrong with acocunt %1', @p1 = @accno

or

   RAISERROR(60012, 16, 1, @accno)

The latter is only legacy and God forbid that anyone uses this today in the system! (We abandoned that track over 10 years ago.)

As for the first, the way it works is that the code is looked up in a table, and if there is a text in the user's language, else we use the text in the system language. If the code is not there at all, the text in the fourth parameter is used. Many messages that are only internal checks that the UI does things right uses a generic code and the free text is all there is. As the example suggest, the text may be parameterised.

---

Erland Sommarskog, SQL Server MVP, [e-mail]