SQL Server v.Next (Denali) : Exploring THROW

AaronBertrand — Mon, 22 Nov 2010 17:20:00 GMT

Sadly, THROW is not mentioned on the Programmability Enhancements (Database Engine) topic of Denali's "What's New" section. So, unless you were at PASS or have been reading the various blogs from the keynotes and other Denali sessions, 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 calls 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:

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, Line 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 without having to set up all of the messages in sys.messages.

The message_id parameter must be an INT (not a BIGINT) >= 50000. If you try to THROW a system message, you will get an error message instead of the error you wanted:

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

Result:

Msg 14088, Level 16, State 1, Line 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, so printf 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, Line 1
There is already a foo named bar.

However, THROW does not accept any parameters for 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, Line 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, Line 2
There is already a foo named bar.

Cumbersome, but it works.

The severity level for THROW is always 16 (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, Line 1
Process ID 55 has raised user error 66668, severity 24. SQL Server is terminating this process.
Msg 2745, Level 16, State 2, Line 1
Process ID 55 has raised user error 66668, severity 24. SQL Server is terminating this process.
Msg 66668, Level 24, State 1, Line 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, Line 1
This is really bad.

There is no THROW equivalent to WITH NOWAIT for immediate buffer output. With RAISERROR we 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 prevents waiting for the buffer to fill up before seeing real-time messages in the results pane of Management Studio.
Unlike RAISERROR, THROW honors XACT_ABORT (see Neils Berglund's post for more information).
There is no way to use WITH LOG in combination with THROW, or to "THROW" a non-severe error (e.g. one that prints in black in Management Studio instead of red, like the above example with severity level 0).

THROW inside CATCH

THROW inside a CATCH block acts like RETHROW - it will re-raise the exception that transferred 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

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:

Error Handling in SQL 2005 and Later

A few more items to consider

Note that THROW() is one more case where the preceding statement needs to end with a proper statement terminator. So just as a gentle reminder, start using those semi-colons!

Also note that there is still a very noticeable omission in the TRY/CATCH error handling model: FINALLY.

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 (http://msdn.microsoft.com/en-us/library/ms178592(SQL.110).aspx):

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. New applications should use THROW instead.

Books Online is currently incorrect, and should be updated soon. There is currently no publicized plan to remove RAISERROR; what the topic should state is that only the old-style RAISERROR is deprecated, e.g.:

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

Of course you can still use this syntax today, but it has been on the deprecation path for some time. If you run the above code on Denali, SQL Server 2008 R2 or even SQL Server 2008, 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;

Search results matching tags 'SQL Server 2011', 'rethrow', 'CTP1', and 'denali'

SQL Server v.Next (Denali) : Exploring THROW