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:

1. Without any parameters within the CATCH block of a TRY/CATCH construct. This will essentially re-throw the original error.
2. With parameters to throw a user defined error.

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 rest
DECLARE@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 TRY
BEGIN 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 = 10
Leaving TRY

-- '0'
Entering TRY
Entering CATCH
  Handling Divide by zero error...
Leaving CATCH

-- '0.0001'
Entering TRY
Entering CATCH
  Handling Arithmetic overflow error converting expression to data type tinyint...
Leaving CATCH

-- 'A'
Entering TRY
Entering CATCH
  Let upper level deal with error...
Msg 8114, Level 16, State 5, Line 8
Error 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 Folder\master.bak';

-- Output
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'c:\Nonexistent Folder\master.bak'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
BACKUP 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 Folder\master.bak';
END TRY
BEGIN 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 Folder\master.bak';
END TRY
BEGIN CATCH
  THROW;
END CATCH;

-- Output
Msg 3201, Level 16, State 1, Line 3
Cannot open backup device 'c:\Nonexistent Folder\master.bak'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 3
BACKUP 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:

• THROW command doesn’t require the error number given to it as first argument to be defined in sys.messages.
• THROW command doesn’t support printf formatting styles for the message like RAISERROR does.
• THROW command (with parameters) always generates a 16 level error unlike RAISERROR where you can control the severity.
• THROW doesn’t support the additional options that RAISERROR does with its WITH clause, like WITH LOG.

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 message
EXEC sp_addmessage 43112609, 16, '%s is prime. It''s not an error but it sure deserves attention!';

-- Format a message and raise an error
DECLARE @msg AS NVARCHAR(2048);
SET @msg = FORMATMESSAGE(43112609, '2^43112609');
THROW 43112609, @msg, 1

-- Output
Msg 43112609, Level 16, State 1, Line 3
2^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