Now I’m sure after the last post , you’re thinking “Oh, ever-so humble author, why, oh, why, would I pick one of these over the other?” Well, I’m glad you’re checking in today, because that’s exactly what we’re going over!
Now, the easiest scenario is if the SQL Server you’re working with is somewhere between SQL Server 7.0 and SQL Server 2012, then you only have one real option – RAISERROR, since it is the only one of the two that exists. If this is the case, you have other things to worry about like End of Mainstream Support for SQL Server 2008 and 2008 R2, but let’s not divert too far from our topic du jour.
If you want the stored procedure to continue on and do whatever processes are left, you should use RAISERROR. Now, having said that, if your data is dependent upon other data that you may have pushed, then this could be a very bad thing to do. Not only does this allow for possible data loss, but now, you may not be able to determine what state your data is in – some could be good, some could be bad, some could be just plain “junk” data. More about these in subsequent posts…
Do you need a specific error number to show that just happens to be a
system exception (i.e. exception with Error Number less than 50000). ? If so, then RAISERROR is your only choice. THROW gives the following error message to the error message:
On the other hand, if the error message should be higher than 50,000? Then THROW may be the option for you. THROW can do it – just change the error number shown above and it’ll work just as expected. RAISERROR can also work, but you have to INSERT the error message – with text – into the sys.messages table first.
But, the real kicker for me – in most cases – is the fact that RAISERROR has been marked for deprecation. Does that mean that you can’t use it any longer? Not even close. Can Microsoft decide one day to stop allowing it to be used in the newest build of SQL Server? Absolutely.
So, to add to your SQL tool-belt, remember that there is a RAISERROR command and a THROW command. Do they both have their uses? Absolutely. Should I be familiar with both of them? Absolutely. Can each of them save your butt the next time you’re tackling an issue with SQL Server. Ab-so-freakin-lute-ly.