Throwing Errors in SQL Server 2012

Error handling in T-SQL was very difficult to implement properly before SQL Server 2005 introduced the TRY/CATCH construct, a feature loosely based on .NET’s try/catch structured exception handling model. The CATCH block gives you a single place to code error handling logic in the event that a problem occurs anywhere inside the TRY block above it. Before TRY/CATCH, it was necessary to always check for error conditions after every operation by testing the built-in system function @@ERROR. Not only did code become cluttered with the many @@ERROR tests, developers (being humans) would too often forget to test @@ERROR in every needed place, causing many unhandled exceptions to go unnoticed.

In SQL Server 2005, TRY/CATCH represented a vast improvement over constantly testing @@ERROR, but RAISERROR has (until SQL Server 2012) remained as the only mechanism for generating your own errors. In SQL Server 2012, the new THROW statement (again, borrowed from throw in the .NET model) is the recommended alternative way to raise exceptions in your T-SQL code (although RAISERROR does retain several capabilities that THROW lacks, as I’ll explain shortly).

Re-Throwing Exceptions

The new THROW statement can be used in two ways. First, and as I just stated, it can serve as an alternative to RAISERROR, allowing your code to generate errors when it detects an unresolvable condition in processing. Used for this purpose, the THROW statement accepts parameters for the error code, description, and state, and works much like RAISERROR. A more specialized use of THROW takes no parameters, and can appear only inside a CATCH block. In this scenario, an unexpected error occurs in the TRY block above, triggering execution of the CATCH block. Inside the CATCH block, you can perform general error handling (for example, logging the error, or rolling back a transaction), and then issue a THROW statement with no parameters. This will re-throw the original error that occurred—with its code, message, severity, and state intact—back up to the client, so the error can be caught and handled at the application level as well. This is an easy and elegant way for you to implement a segmented exception handling strategy between the database and application layers.

In contrast, RAISERROR always raises a new error. Thus, it can only simulate re-throwing the original error by capturing the ERROR_MESSAGE, ERROR_SEVERITY, and ERROR_STATE in the CATCH block and using their values to raise a new error. Using THROW for this purpose is much more simple and direct, as demonstrated with the following code:

CREATE TABLE ErrorLog(ErrAt datetime2, Severity varchar(max), ErrMsg varchar(max))
GO

BEGIN TRY
  DECLARE @Number int = 5 / 0;
END TRY
BEGIN CATCH
  -- Log the error info, then re-throw it
  INSERT INTO ErrorLog VALUES(SYSDATETIME(), ERROR_SEVERITY(), ERROR_MESSAGE());
  THROW;
END CATCH

In this code’s CATCH block, error information is recorded to the ErrorLog table and then the original error (divide by zero) is re-thrown for the client to catch:

Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.

To confirm that the error was logged by the CATCH block as expected before being re-thrown, query the ErrorLog table:

SELECT * FROM ErrorLog
ErrAt                     Severity ErrMsg
------------------------- -------- ------------------------------------------
2011-10-30 14:14:35.336   16       Divide by zero error encountered.

Comparing THROW and RAISERROR

The following table summarizes the notable differences between THROW and RAISERROR:

THROW RAISERROR
Can only generate user exceptions (unless re-throwing in CATCH block) Can generate user (>= 50000) and system (< 50000) exceptions
Supplies ad-hoc text; doesn’t utilize sys.messages Requires user messages defined in sys.messages (except for code 50000)
Doesn’t support token substitutions Supports token substitutions
Always uses severity level 16 (unless re-throwing in a CATCH block) Can set any severity level
Can re-throw original exception caught in the TRY block Always generates a new exception; the original exception is lost to the client
Error messages are buffered, and don’t appear in real-time Supports WITH NOWAIT to immediate flush buffered output on error

A user exception is an error with a code of 50000 or higher that you define for your application’s use. System exceptions are defined by SQL Server and have error codes lower than 50000. You can use the new THROW statement to generate and raise user exceptions, but not system exceptions. Only RAISERROR can be used to throw system exceptions. Note, however, that when THROW is used in a CATCH block to re-throw the exception from a TRY block, the actual original exception—even if it’s a system exception—will get thrown (as demonstrated in the previous “divide by zero” example).

When RAISERROR is used without an error code, SQL Server assigns an error code of 50000 and expects you to supply an ad-hoc message to associate with the error. The THROW statement always expects you to supply an ad-hoc message for the error, as well as a user error code of 50000 or higher. Thus, the following two statements are equivalent:

THROW 50000, 'An error occurred querying the table.', 1;
RAISERROR ('An error occurred querying the table.', 16, 1);

Both these statements raise an error with code 50000, severity 16, state 1, and the same message text. Compatibility between the two keywords ends there, however, as varying usages impose different rules (as summarized in Table 2-4). For example, only RAISERROR supports token substitution:

RAISERROR ('An error occurred querying the %s table.', 16, 1, 'Customer');
Msg 50000, Level 16, State 1, Line 22
An error occurred querying the Customer table.

THROW has no similar capability. Also, while RAISERROR lets you specify any severity level, THROW will always generate an error with a severity level of 16. This is significant, as level 11 and higher indicates more serious errors than level 10 and lower. For example:

RAISERROR ('An error occurred querying the table.', 10, 1);
An error occurred querying the table.

Because the severity is 10, this error does not echo the error code, level, state, and line number, and is displayed in black rather than the usual red that is used for severity levels higher than 10. In contrast, THROW cannot be used to signal a non-severe error.

The last important difference between the two keywords is the RAISERROR association with sys.messages. In particular, RAISERROR requires that you call sys.sp_addmessage to define error messages associated with user error codes higher than 50000. As explained, the RAISERROR syntax in our earlier examples uses an error code of 50000, and is the only supported syntax that lets you supply an ad-hoc message instead of utilizing sys.messages.

The following code demonstrates how to define customer user error messages for RAISERROR. First (and only once), a tokenized message for user error code 66666 is added to sys.messages. Thereafter, RAISERROR references the error by its code, and also supplies values for token replacements that are applied to the message’s text in sys.messages.

EXEC sys.sp_addmessage 66666, 16, 'There is already a %s named %s.';
RAISERROR(66666, 16, 1, 'cat', 'morris');
Msg 66666, Level 16, State 1, Line 34
There is already a cat named morris.

The THROW statement has no such requirement. You supply any ad-hoc message text with THROW. You don’t need to separately manage sys.messages, but this also means that THROW can’t (directly) leverage centrally managed error messages in sys.messages like RAISERROR does. Fortunately, the FORMATMESSAGE function provides a workaround, if you want to take advantage of the same capability with THROW. You just need to take care and make sure that the same error code is specified in the two places that you need to reference it (once for FORMATMESSAGE and once for THROW), as shown here:

DECLARE @Message varchar(max) = FORMATMESSAGE(66666, 'dog', 'snoopy');
THROW 66666, @Message, 1;
Msg 66666, Level 16, State 1, Line 40
There is already a dog named snoopy.

Summary

Starting in SQL Server 2012, the THROW keyword should be used instead of RAISERROR to raise your own errors. THROW can also be used inside CATCH blocks to raise the original error that occurred within the TRY block. However, RAISERROR is still supported, and can be used to raise system errors or errors with any lesser severity, when necessary. You can start working with THROW by downloading SQL Server 2012 “Denali” CTP3 from http://bit.ly/DenaliCTP3. Try not too have too much fun throwing errors! 😉

Advertisements

Book Announcement! Introducing “Programming Microsoft SQL Server 2012”

I’m very happy to announce that I’ll be authoring a new book on SQL Server 2012, code-named “Denali.” The full title, “Programming Microsoft SQL Server 2012” will technically be published by O’Reilly, yet it will be branded as a Microsoft Press book. O’Reilly recently acquired MS Press, but retained the Microsoft logo and black/red theme, which I’m really glad about. Although O’Reilly is a big name, the MS Press branding still carries more clout. And to be honest, I greatly prefer having a cool tool to a weird animal on the cover.

This book is an update to the SQL Server 2008 edition I wrote three years ago, with many similarities but also some very notable differences. I’m extremely pleased to once again team together with Andrew Brust, who will be writing the chapters on SQL CLR, column stores, BI, and SQL Azure. Andrew was my co-author for the 2008 edition and lead author of the original 2005 edition, so this is actually his third time around on this book. Both previous editions literally burst at the seams in their zeal to cover the entire SQL Server stack. This time around, in consideration of the many readily available BI-focused books, the editors decided to distill the BI footprint of the new Denali book (which accounted for roughly a whole third of the previous 1,000+ page 2008 edition) down to a single overview-style chapter. This simultaneously brings the page count down a little, while actually opening more space for expanded coverage of the relational database engine. As a result, this new book is more narrowly (and uniquely) focused on programming SQL Server for transactional line-of-business applications built with .NET and Visual Studio.

So busy days lie ahead. I’ve actually spent nearly a year already gearing up on Denali, since attending the Denali SDR in Redmond last October. Here are just some of the new topics planned for the 2012 edition:

  • SQL Server Data Tools
  • SQL Azure
  • Column store indexes
  • Sequences
  • Windowing function (OVER BY) improvements
  • Server-side paging
  • FileTable (FILESTREAM + hierarchyid = logical file system)
  • Metadata discovery
  • Spatial enhancements (curves, FULLGLOBE)
  • Contained Databases
  • Self-Service Reporting (Crescent)
  • Entity Framework & LINQ
  • WCF Data Services & WCF RIA Services

This list is by no means exhaustive, and may still change, but it does give a good idea of what to expect. Also, this is in addition to updated coverage from the previous edition that includes broader treatment of topics such as table-valued parameters, SQL Server Audit, and more.

With luck, the book should be out by Q2 2012. I’m looking forward to the work in store, and hope to produce the best piece of work I can. Along the way, I’ll be blogging more previews of what’s to come. So stay tuned, and thanks for reading.