SQL Server 2012 T-SQL at a Glance – THROW

SQL Server made a big leap with its error handling support in SQL Server 2005 where the TRY/CATCH construct was initially introduced; but then SQL Server 2008 didn’t really add any new error handling features. SQL Server 2012 (formerly code-named Denali) addresses important needs that were not covered so far by introducing the THROW command.

The THROW command can be invoked in two main ways:

I’ll start with the former. First, you need to be aware that executing THROW without parameters is only allowed within a CATCH block. Also, whether you execute it with or without parameters, the statement before THROW must be terminated with a semicolon—one more reason to try and get to the habit of simply terminating all of your statements with a semicolon.

At any rate, I wanted to touch on a couple of important scenarios that I struggled with in the past, and that are now addressed elegantly with the new parameterless THROW. One scenario is when you want to deal with a certain set of errors in one CATCH block, but let the upper level in the call stack deal with the rest. In the past you had to raise a user-defined error for the caller to deal with, making it very awkward to pass and interpret the error information—it wasn’t the original error. Now you simply invoke THROW in the ELSE block of your error handling logic. Here’s an example:

-- Rethrowing errors- e.g., deal with some errors in current level, and let upper level deal with restDECLARE@r AS tinyint, @i AS VARCHAR(10) = '1'; -- try with '1', '0', '0.0001', 'A';SET NOCOUNT ON;BEGIN TRY    PRINT 'Entering TRY';  SET @r = 10./CAST(@i AS NUMERIC(20, 10));  PRINT '  @r = ' + CAST(@r AS VARCHAR(11));  PRINT 'Leaving TRY';END TRYBEGIN CATCH  PRINT 'Entering CATCH';  IF ERROR_NUMBER() = 8134  BEGIN    PRINT '  Handling Divide by zero error...';  END  ELSE  IF ERROR_NUMBER() = 8115  BEGIN    PRINT '  Handling Arithmetic overflow error converting expression to data type tinyint...';  END  ELSE  BEGIN    PRINT '  Let upper level deal with error...';    THROW;  END;  PRINT 'Leaving CATCH';END CATCH;-- Outputs for different tests:-- '1'Entering TRY  @r = 10Leaving TRY-- '0'Entering TRYEntering CATCH  Handling Divide by zero error...Leaving CATCH-- '0.0001'Entering TRYEntering CATCH  Handling Arithmetic overflow error converting expression to data type tinyint...Leaving CATCH-- 'A'Entering TRYEntering CATCH  Let upper level deal with error...Msg 8114, Level 16, State 5, Line 8Error converting data type varchar to numeric. 

In this example I wrote code that handles errors 8134 (divide by zero) and 8115 (arithmetic overflow), but I let the upper level deal with the rest of the errors (e.g., 8114, which is a conversion error).

Another important scenario that the new THROW covers is cases where a single error causes multiple error messages to be generated. Consider the following error for example due to a backup target that doesn’t exist:

BACKUP DATABASE master TO DISK = 'c:Nonexistent Foldermaster.bak';-- OutputMsg 3201, Level 16, State 1, Line 1Cannot open backup device 'c:Nonexistent Foldermaster.bak'. Operating system error 3(The system cannot find the path specified.).Msg 3013, Level 16, State 1, Line 1BACKUP DATABASE is terminating abnormally.

If you try to capture error information like error number and message, you get only the last:

BEGIN TRY  BACKUP DATABASE master TO DISK = 'c:Nonexistent Foldermaster.bak';END TRYBEGIN CATCH  PRINT '  Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10));  PRINT '  Error Message: ' + ERROR_MESSAGE();END CATCH;

-- Output:   Error Number: 3013  Error Message: BACKUP DATABASE is terminating abnormally. 

But if you need to rethrow the error, the THROW command correctly generates all applicable error messages:

BEGIN TRY  BACKUP DATABASE master TO DISK = 'c:Nonexistent Foldermaster.bak';END TRYBEGIN CATCH  THROW;END CATCH;

-- OutputMsg 3201, Level 16, State 1, Line 3Cannot open backup device 'c:Nonexistent Foldermaster.bak'. Operating system error 3(The system cannot find the path specified.).Msg 3013, Level 16, State 1, Line 3BACKUP DATABASE is terminating abnormally. 

As for THROW with parameters, it is allowed outside of a CATCH, but it will cause the session to end, the severity to be set to 16, and the line number and procedure where the error was raised to be set. The THROW command accepts three parameters: the error number, message and state. There are a few differences you want to be aware of between THROW with parameters and RAISERROR:

If you wish to construct a message based on one created with sp_addmessage in  sys.messages, including support for parameters, you can use a function called FORMATMESSAGE for this purpose. You use FORMATMESSAGE to format the message, and then THROW to raise it. Here’s an example:

-- Add messageEXEC sp_addmessage 43112609, 16, '%s is prime. It''s not an error but it sure deserves attention!';-- Format a message and raise an errorDECLARE @msg AS NVARCHAR(2048);SET @msg = FORMATMESSAGE(43112609, '2^43112609');THROW 43112609, @msg, 1-- OutputMsg 43112609, Level 16, State 1, Line 32^43112609 is prime. It's not an error but it sure deserves attention! 

There are some important features related to error handling that are still missing, but the new ones do cover very important and practical needs.

Cheers,

BG