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! 😉
Lenni's Technology Blog 