Raising Exceptions and Error Handling with SQL Server THROW

By:   |   Updated: 2022-10-11   |   Comments   |   Related: More > Error Handling


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;
Error Message

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;
Error Message

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;
Error Message

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)
Error Message

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;
Error Message

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





get scripts

next tip button



About the author
MSSQLTips author Jared Westover Jared Westover (@westoverjared) is a SQL Server specialist with nearly two decades of industry experience.

View all my tips


Article Last Updated: 2022-10-11

Comments For This Article

















get free sql tips
agree to terms