SQL Server v.Next (Denali) : Exploring THROW

Sadly, THROW is not mentioned in the Programmability Enhancements (Database Engine) topic of Denali's "What's New" section. So, unless you were at PASS Summit, have been reading the various blogs from the keynotes and other Denali sessions, or happened upon the command's own topic, the presence of this keyword may be news to you. I wanted to touch briefly on what THROW can do and, more importantly, what it can't do – before you start thinking about abandoning all usage of RAISERROR (or "RAISE ROAR" as Tobias Ternstrom has always called it).

How THROW and RAISERROR differ

THROW outside of a CATCH block acts similar to RAISERROR, with a few notable exceptions:

  • The message_id parameter does not need to be defined in sys.messages.
    Unlike with RAISERROR, you can throw any error number in the 50K+ range:

    THROW 66666, 'Hi', 1;

    Result:

    Msg 66666, Level 16, State 16, Line 1
    Hi

    With RAISERROR, you can use a string for the first parameter, but if you use a number that is not represented in sys.messages:

    RAISERROR(54321, 16, 1);

    Result:

    Msg 18054, Level 16, State 1
    Error 54321, severity 16, 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.

    Why should you care? Well, if you have applications that rely on certain error message numbers coming out of SQL Server (and ignore the text of the error message), converting to THROW will allow you to migrate to new servers (or add nodes to existing Availability Groups) without having to set up all of the messages in sys.messages.

  • The message_id parameter must be an int >= 50000.
    If you try to THROW a system message, you will get an error message (and potentially an additional, unrelated error message) instead of the error you intended:

     RAISERROR(14088, 16, 1, N'foo');
    GO
    THROW 14088, N'foo', 1;

    Result:

    Msg 14088, Level 16, State 1
    The table 'foo' must have a primary key to be published using the transaction-based method.
    Msg 35100, Level 16, State 10, Line 1
    Error number 14088 in the THROW statement is outside the valid range. Specify an error number in the valid range of 50000 to 2147483647.
  • There is no token substitution within the command itself
    Substitution doesn't work, so printf-style formatting is ignored. Let's say we've added this message to sys.messages:

    EXEC sys.sp_addmessage 
        @msgnum   = 66667,
        @severity = 16,
        @msgtext  = N'There is already a %s named %s.';

    With RAISERROR, we can simply say:

    RAISERROR(66667, 16, 1, N'foo', N'bar');

    Result:

    Msg 66667, Level 16, State 1
    There is already a foo named bar.

    However, THROW does not accept any parameters for token substitution. If you try:

    THROW 66667, N'There is already a %s named %s.', 1, N'foo', N'bar';

    Result:

    Msg 102, Level 15, State 1
    Incorrect syntax near ','.

    Since repeating the text defeats the purpose of saving the error message in sys.messages in the first place, you can get around this using FORMATMESSAGE():

    DECLARE @msg NVARCHAR(2048) = FORMATMESSAGE(66667, N'foo', N'bar');
    THROW 66667, @msg, 1;

    Result:

    Msg 66667, Level 16, State 1
    There is already a foo named bar.

    Cumbersome, but it works.

  • The severity level for THROW is always 16

    Well, unless it is a re-THROW inside CATCH. And there is no way to force the breaking of the connection like we can with RAISERROR. Assume we have added this error message:

     EXEC sys.sp_addmessage 
        @msgnum   = 66668,
        @severity = 24,
        @msgtext  = N'This is really bad.';

    If we use RAISERROR, we can obey the severity level by re-specifying and using WITH LOG:

    RAISERROR(66668, 24, 1) WITH LOG;

    Result:

    Msg 2745, Level 16, State 2
    Process ID 55 has raised user error 66668, severity 24. SQL Server is terminating this process.
    Msg 2745, Level 16, State 2
    Process ID 55 has raised user error 66668, severity 24. SQL Server is terminating this process.
    Msg 66668, Level 24, State 1
    This is really bad.
    Msg 0, Level 20, State 0, Line 0
    A severe error occurred on the current command. The results, if any, should be discarded.

    There is no way to do that with THROW:

    THROW 66668, N'This is really bad.', 1;

    Result:

    Msg 66668, Level 16, State 1
    This is really bad.
  • The statement before THROW must be terminated
    If you're not diligent about terminating your statements with semi-colons, you could be in for a surprise:

    BEGIN TRANSACTION
    BEGIN TRY
      SELECT 1/0
      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
      ROLLBACK TRANSACTION
      THROW
    END CATCH

    Result (don't forget to issue a manual ROLLBACK TRANSACTION):

    Msg 6401, Level 16, State 1
    Cannot roll back THROW. No transaction or savepoint of that name was found.

    You should always add semi-colons to almost every statement (note the exception here):

    BEGIN TRANSACTION;
    BEGIN TRY;
      SELECT 1/0;
      COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH;
      ROLLBACK TRANSACTION;
      THROW;
    END CATCH;
  • THROW is fussy about certain characters
    The % character in particular is problematic for THROW. For example:

    THROW 66671, N'This is %not nice', 1;
    GO
    THROW 66672, N'This is %terrible', 1;

    Result:

    Msg 66671, Level 16, State 1, Line 1
    This is
    not nice

    Msg 66672, Level 16, State 1, Line 3
     

    Something to keep in mind if you're transitioning existing messages from RAISERROR.

  • THROW does not support NOWAIT

    For immediate buffer output, RAISERROR can do this:

     RAISERROR(N'Printing status...', 0, 1) WITH NOWAIT;

    This can be quite useful in place of PRINT when monitoring a query with multiple steps, as it avoids having to wait the buffer to fill up before seeing real-time messages in the results pane of Management Studio.

Unlike RAISERROR, THROW honors XACT_ABORT

Most of the above show behaviors to watch out for with THROW, this is one there the new functionality has better behavior. See Neils Berglund's post for more information.

THROW inside CATCH

THROW inside a CATCH block acts like RETHROW – it will re-raise the exception that transferred control to the CATCH block in the first place. An example:

BEGIN TRY;
    RAISERROR(N'Hi from try.', 16, 1);
END TRY
BEGIN CATCH;
    PRINT N'Hi from inside CATCH.';
    THROW;
END CATCH;

Result:

Hi from inside CATCH.
Msg 50000, Level 16, State 1, Line 2
Hi from try.

This will allow you to perform other tasks, such as rollbacks, notifications, etc. before eventually throwing the same errors back to the caller – without having to understand and re-code all of the possible outcomes from within the TRY block. Note that THROW inside CATCH can re-throw multiple errors, not just the most recent one (which is all you have access to from ERROR_MESSAGE() and its cousins). And this is the only place where THROW by itself is valid syntax.

I am not going to pretend to be an expert in error handling, so I will leave it up to Erland to give you a great primer:

FINALLY

There is still a very noticeable omission in the TRY/CATCH error handling model: FINALLY. Why would FINALLY be useful? Let's say you have an error when trying to roll back a transaction, as in the example above:

...
BEGIN CATCH
  ROLLBACK TRANSACTION 
  THROW
END CATCH
SELECT @@TRANCOUNT; -- NOT ZERO!

And finally (no pun intended), some housekeeping

The current version of Denali's Books Online indicates that RAISERROR is deprecated. From the RAISERROR (Transact-SQL) topic):

New applications should use THROW instead.

Books Online had previously stated that RAISERROR was deprecated, but this was incorrect (and has since been corrected). The topic should state that only the old-style RAISERROR is deprecated, e.g.:

RAISERROR 54321 N'This is an old-style RAISERROR.';

While technically you can still use this syntax today, it has been on the deprecation path for some time. If you run the above code on modern versions, you will see this performance counter increase:

SELECT [object_name], instance_name, cntr_value
    FROM sys.dm_os_performance_counters
    WHERE [object_name] LIKE N'%:Deprecated Features%'
    AND instance_name = N'Oldstyle RAISERROR'
    AND cntr_value > 0;

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at SQLPerformance and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a father of two, a huge hockey and football fan, and my pronouns are he/him.

7 Responses

  1. AaronBertrand says:

    @Aasif sorry about the delay, just noticed this comment now. I am not sure how THROW in and of itself changes your ability to use an output parameter, but remember, just because you've moved to 2012 does not mean you have to go changing every single instance of RAISERROR to THROW.

  2. Aasif Risad says:

    Sir
    I am stuck in THROW statement.
    I am using SQL Server 2012, previously I created the sp on SQL Server 2005 and used RAISERROR.
    Now it has to be migrated in SQL Server 2012 & I have applied THROW in place of raiserror but when I use throw then I am not able to get the value of OUTPUT PARAMETER in my code.
    It gets only nothing with OUT PARAMETER before it is working with RAISERROR and I am able to access the OUT PARAMETER value.
    Please help me as I am stuch in this.
    How can I get the output param value in code if I am using the throw keyword in sql server 2012 ?????
    Regards,
    Aasif Risad

  3. Viking says:

    Thanks for sharing your thoughts once again, Aaron. I hope to see corrected text.

  4. AaronBertrand says:

    Viking,
    The blurb from Books Online I copied that says "avoid using this for new development work" was an over-eager mistake. They did not mean to imply that RAISERROR was deprecated or that THROW should be used in its place. I think the author that was charged with updating the topic just didn't understand that only a very small subset of RAISERROR functionality (which I describe above) is being deprecated, and instead treated it as if the whole feature was being removed (and implied that all future development work would target only Denali, which is kind of laughable given the number of SQL Server 2000 instances still in existence). You should see corrected text in the next update of Denali's Books Online.

  5. Viking says:

    Quick question Aaron – if RAISERROR isn't the recommendation for future development then what happens to the code that is being developed on R2. I don't know or probably I think there is no equivalent of THROW in R2 at least. How do you think this could be mitigated? Just curious to know on how you would look at this.

  6. Louis Davidson says:

    I miss that old syntax:
    RAISERROR 54321 N'This is an old-style RAISERROR.';
    Of course, it still works, but I dutifully stopped using it back when they told us too. Glad that THROW will give this back to us law abiding SQL types.

  7. Roger Noble says:

    Great post Aaron, I agree that the inclusion of FINALLY would make error handling more complete