When bulding solid T-SQL solutions, it is often necessary to implement some level of error handling. One of the most used features provided by SQL Server, is RAISERROR. This has been around since SQL Server 7.0, but with SQL Server 2005 TRY/CATCH was born. This significally improved error handling which now resembles error handling as we know it from modern development languages like C#.

Now error handling has been improved even more, with the introduction of THROW in Denali.

One of the differences between TRHOW and RAISERROR is that THROW always raises an error with severity 16. This cannot be changed. An exampel of usage:

THROW 50000, 'This is a custom error message', 1

This gives the following error message as expected:

Msg 50000, Level 16, State 1, Line 1
This is a custom error message

So, why is this any different than RAISERROR?

One of the problems with RAISERROR is that it does not honor the XACT_ABORT option if set to ON.

Example:

SET XACT_ABORT ON
BEGIN TRAN
    INSERT INTO MyTable (col1) VALUES (1);
    THROW 50000, 'This is my custom error being raised', 1
    INSERT INTO MyTable (col1) VALUES (2)
COMMIT
SET XACT_ABORT OFF

The output of this query is:

(1 row(s) affected)
Msg 50000, Level 16, State 1, Line 4
This is my custom error being raised

In this example we throw an error in the middle of a transaction, with XACT_ABORT ON. The output shows that the first insert is performed with 1 row affected. Then the error is raised, and the second insert is never executed. The entire transaction is automatically rolled back, leaving no inserted rows in the table.

If we however used RAISERROR instead of THROW, the results would have been different:

SET XACT_ABORT ON
BEGIN TRAN
    INSERT INTO TransactionTest (val) VALUES (1);
    RAISERROR('This is my custom error being raised', 16, 1)
    INSERT INTO TransactionTest (val) VALUES (2)
COMMIT
SET XACT_ABORT OFF

Results:

(1 row(s) affected)
Msg 50000, Level 16, State 1, Line 4
This is my custom error being raised
(1 row(s) affected)

The exact same error is still raised, but now the second insert is also performed, and the automatic rollback is not performed.

This is one of the minor improvements of THROW. Another big change is the possibility to re-throw an error back the the upper level. Imagine you have some code within a TRY block, where a number of different errors might occur. You might want to handle “divide by zero” errors directly in the t-sql code, but let the upper level handle all other errors. With RAISERROR this cannot be achieved, because you can only raise user defined errors, with error number higher than 50000. So if you encounter ERROR_NUMBER() = 8114 in  your CATCH block, your only option is to raise a new user defined  error having the same meaning. Etc. error number 51000 = “Error converting data type varchar to numeric.” This is no way elegant.

This can easily be solved using THROW:

DECLARE @str VARCHAR(10) = '3'
DECLARE @i INT
SET NOCOUNT ON
BEGIN TRY
    SET @i = CAST(@str AS INT)
    SET @i = 10/@i
    PRINT @str + ' was successfully converted to an INT'
    PRINT 'Division successfull'
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 8134
    BEGIN
        PRINT 'Divide by zero error occurred. Logging error.'
    END
    ELSE
    BEGIN
        PRINT 'Other error occorred with @str = ''' + @str + '''. Re-throw the error to upper level for handling...';
        THROW;
    END
END CATCH;

The input string @str is first converted to an integer, and then the number is used in a division. This requres that the value of @str is an integer, and it should be different than 0.

The conversion and division is located in the TRY block, and depending on the input string, either an 8134 or 245 error is raised – or no error at all. Within the CATCH block we check for error 8134, making it possible to log that specific error. If the error is different than 8134, then we simply invoke “THROW;”.

THROW without any arguments is only allowed within a CATCH block, and simply re-throws the original error, letting the upper level handle the error.

When executing the code with @str = ‘3’ no error occurs, and the message output is:

3 was successfully converted to an INT
Division successfull

Executiong with @str = ‘0’ would give a “divide by zero” error, which should be handled. The output is:

Divide by zero error occurred. Logging error.

With @str = ‘aNumber’ the following error is re-thrown:

Other error occorred with @str = 'aNumber'. Re-throw the error to upper level for handling...
Msg 245, Level 16, State 1, Line 6
Conversion failed when converting the varchar value 'aNumber' to data type int.

The introduction of THROW simply takes error handling to the next level.