Problem
Have you considered adding error handling to your Transact-SQL (T-SQL) code? If you ask most seasoned developers, they likely agree it’s a good idea. Perhaps you’ve inherited less-than-ideal code. Or your code could use a bit of sprucing up. One of the primary reasons to add error handling is to be in control of when exceptions occur. It would be nice if errors didn’t pop up, but that world doesn’t exist. There are a couple of ways you can raise exceptions in T-SQL. The older method is with RAISERROR statement. Now RAISERROR still has its uses, but since SQL Server 2012, a new player has been in town called THROW.
Solution
In this tutorial, we’ll look at using THROW. I’ll explore some of the benefits and a few drawbacks. By the end, you’ll be able to make an informed decision on which solution to implement with your SQL Server data.
Using the SQL Server THROW Statement
Typically, you see THROW used inside of a TRY…CATCH block. However, you can use THROW with parameters on its own. Let’s look at a simple example. You can run all the scripts in this tutorial with SQL Server Management Studio (SSMS) without setting up a SQL Server database.
THROW 50000, 'Houston, we have a problem.', 1;
For the example above to work, you must provide a message ID, text, and state. What’s nice about THROW is that the message ID doesn’t need to exist in sys.messages. I always seem to use 1 for the state.
As mentioned above, you commonly see THROW used in conjunction with a TRY…CATCH block as in the example below.
BEGIN TRY -- TRY BLOCK
SELECT 1 / 0; -- SELECT statement
END TRY
BEGIN CATCH -- CATCH BLOCK
THROW;
END CATCH;
One of the remarkable things about THROW, when used inside a TRY…CATCH, you only need to type THROW for it to work. If there was an easy button for raising an exception, this must be it.
Advantages of THROW
Let’s spend a minute and look at a few of the top benefits of THROW, specifically when compared to RAISERROR. Right off the bat, the simplicity of THROW is unmatched. However, here are a few other great features.
Error Message and Line Number
Please look again at the screenshot above. You get the actual message returned by simply typing THROW. You don’t need to worry about using the error functions. Also, notice the line number. That’s the line number where the exception occurred, not where we called THROW in the code block. Imagine debugging 1000-plus lines of code. Getting an accurate number is critical to troubleshooting when errors pop up.
Message ID
If you use THROW outside or inside of a TRY…CATCH, you don’t need to worry about the message ID existing. You can use any old number. With RAISERROR, you need to add the message to sys.messages for this to work.
Batch Termination
When I encounter an exception, I usually want the transaction to ROLLBACK and the batch to terminate. When you execute THROW, the batch stops, and SQL doesn’t run any further statements. Notice in the example below that the PRINT doesn’t run. Here is the syntax:
BEGIN TRY
SELECT 1 / 0;
END TRY
BEGIN CATCH
THROW;
PRINT 'I sure hope this works!';
END CATCH;
Microsoft Recommends
Finally, since SQL Server 2012, Microsoft has recommended using THROW. Of course, this depends on why you’re using THROW in the first place. I still use RAISERROR for specific use cases, as you’ll see in the next section.
Drawbacks of THROW
Now let’s spend a minute looking at a few of the disadvantages of THROW. Here are a few that developers commonly bring up.
Informational Messages
One of the first drawbacks of THROW is that you can’t raise informational messages. With RAISERROR, you can use lower severity levels. For example, a severity of 10 returns an informational message to the user. This single drawback keeps RAISERROR in the game. Notice the black text in the screenshot below.
RAISERROR ('Now you know. And knowing is half the battle.',10,1)
Batch Termination
I listed batch termination as an advantage but also a disadvantage. If you want to continue after encountering an exception, you’re out of luck. This behavior is another reason I still use RAISERROR occasionally. When talking about THROW, I sometimes compare it to a football game where the umpire throws a flag onto the field. When a flag’s thrown, the game stops.
Lack of NOWAIT and WITHLOG
With RAISERROR, you can choose to log messages to the SQL Server error log. You can also raise an exception before the entire batch completes. THROW doesn’t offer either of these. I rarely use these options, so not a deal breaker for me.
Statement Terminator
Some people get hung up on needing to terminate the prior statement before THROW. It’s not a massive deal for me but let me give an example. If you execute the statement below, you’ll get an error message during the CATCH.
BEGIN TRY
BEGIN TRANSACTION;
SELECT 1 / 0;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION
THROW;
END CATCH;
To work around this, add a semi-colon after the ROLLBACK TRANSACTION or before THROW. I’ve used statement terminators in my code for at least 10 years and encourage others to do the same.
Which One to Choose
The movie Highlander was one of the best things to come out of the 80s. The tagline was, "There can be only one." Unlike the movie, you don’t have to choose one. You could use the code below if you’re looking for a vanilla method for raising exceptions. Please feel free to copy and paste it into a template.
BEGIN TRY
BEGIN TRANSACTION;
-- Do something really cool;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
ROLLBACK TRANSACTION;
THROW;
END CATCH;
However, for the drawbacks mentioned above, don’t forget about RAISERROR. The one you choose is dependent on your goals. It would be so much easier for me to say you should always do this or that, but as with most things in SQL Server, the answer is that it depends. Please let me know in the comments below your experiences with THROW. I would also like to hear how vital error handling is for you and your organization.
Next Steps
- If you’re not currently using error handling, please consider adding it to at least one script or stored procedure this week. What do you have to lose?
- Would you like a detailed overview of using TRY…CATCH in SQL Server? Please check out this tip, Error Handling in SQL Server with Try Catch by Joydip Kanjilal.
- Aaron Bertrand wrote an excellent article on the merits of adding semi-colons to your T-SQL code. If you’re not currently using them, you might want to start.
- Check out these additional tips:
