New THROW statement in SQL Server 2012 (vs RAISERROR)


The new THROW keyword introduced in SQL server 2012 is an improvement over the existing RAISERROR() statement. Yes, it’s single ‘E’ in RAISERROR.

Both RAISERROR & THROW can be used in T-SQL code/script to raise and throw error within a TRY-CATCH block. Check my previous post for TRY-CATCH block, [link].

>> With RAISERROR developers had to use different ERROR_xxxx() system functions to get the error details to pass through the RAISERROR() statement, like:
- ERROR_NUMBER()
- ERROR_MESSAGE()
- ERROR_SEVERITY()
- ERROR_STATE()

let’s see an example:

-- Using RAISERROR()
DECLARE
@ERR_MSG AS NVARCHAR(4000)
,@ERR_SEV AS SMALLINT
,@ERR_STA AS SMALLINT

BEGIN TRY
SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
SELECT @ERR_MSG = ERROR_MESSAGE(),
@ERR_SEV =ERROR_SEVERITY(),
@ERR_STA = ERROR_STATE()
 SET @ERR_MSG= 'Error occurred while retrieving the data from database: ' + @ERR_MSG

RAISERROR (@ERR_MSG, @ERR_SEV, @ERR_STA) WITH NOWAIT
END CATCH
GO

Output:
(0 row(s) affected)
Msg 50000, Level 16, State 1, Line 15
Error occurred while retrieving the data from database: Divide by zero error encountered.

The RAISERROR() can take first argument as message_id also instead of the message. But if you want to pass the message_id then it has to be in sys.messages

>> With THROW the benefit is: it is not mandatory to pass any parameter to raise an exception.
Just using the THROW; statement will get the error details and raise it, as shown below:

-- Using THROW - 1
BEGIN TRY
SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
THROW;
END CATCH
GO

Output:
(0 row(s) affected)
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.

As you see in the Output above, the error message thrown is the default one. But you can also add your customized message, we will see below.

IMP NOTE: Default THROW statement will show the exact line where the exception was occurred, here the line number is 2 (highlighted GREEN above). But RAISERROR() will show the line number where the RAISERROR statement was executed i.e. Line 15 (highlighted YELLOW above) and not the actual exception.

 

Also passing the message_id won’t require it to be stored in sys.messages, let’s check this:

-- Using THROW - 2
DECLARE
@ERR_MSG AS NVARCHAR(4000)
,@ERR_STA AS SMALLINT

BEGIN TRY
SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
SELECT @ERR_MSG = ERROR_MESSAGE(),
@ERR_STA = ERROR_STATE()

SET @ERR_MSG= 'Error occurred while retrieving the data from database: ' + @ERR_MSG;

THROW 50001, @ERR_MSG, @ERR_STA;
END CATCH
GO

Output:
(0 row(s) affected)
Msg 50001, Level 16, State 1, Line 14
Error occurred while retrieving the data from database: Divide by zero error encountered.

But if you parameterize the THROW statement as above it will not show the actual position of exception occurrence, and the behavior will be same as RAISERROR(). As with RAISERROR() you've to provide mandatory params, so there is no way to get the actual position of Line where the error occurred.

As per MSBOL following are the difference between RAISERROR & THROW:

RAISERROR statement

THROW statement

If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.

The error_number parameter does not have to be defined in sys.messages.

The msg_str parameter can contain printf formatting styles.

The message parameter does not accept printf style formatting.

The severity parameter specifies the severity of the exception.

There is no severity parameter. The exception severity is always set to 16.

NOTE: As per MS BOL for exception handling in new development work THROW must be used instead of RAISERROR.


Source: from my personal blog SQLwithManoj: http://sqlwithmanoj.wordpress.com/2012/12/20/new-throw-statement-in-sql-server-2012-vs-raiserror/

Comments (7)

  1. zorro-cool says:

    Interesting post! Thank you! Does the last note mean that Microsoft intend to make the raiserror function deprecated in the future?

  2. The RAISERROR link on msdn [msdn.microsoft.com/…/ms178592.aspx] suggest to use THROW instead of RAISERROR.

    Not confirmed as the msdn help does not says about deprication.

  3. SteveF says:

    I like the fact Raiserror allows informational errors (severity 10) and the WITh LOG option for use with alerts.  Throw might be useful in some situations but I hope Raiserror and sp_addmessage are kept.

  4. Bozola says:

    " improvement over the existing RAISERROR()"

    You are implying that THROW is a functional replacement for RAISERROR. This is incorrect. THROW contains extra non-optional functionality that is not in RAISERROR.

    THROW is basically "RAISERROR then EXIT". EXITing immediately after a RAISERROR is fundamentally different behavior and cannot be considered to be a "replacement" function.

    This can be seen with this code:

    drop Procedure dbo.xTestRaiserror

    go

    create Procedure dbo.xTestRaiserror

    as

    set nocount on

       DECLARE  @ERR_MSG   NVARCHAR(4000),

                @ERR_SEV   SMALLINT,

                @ERR_STA   SMALLINT

       BEGIN TRY

           SELECT 1/0 DivideByZero

       END TRY

       BEGIN CATCH

           SELECT  @ERR_SEV = ERROR_SEVERITY(),

                   @ERR_STA = ERROR_STATE(),

                   @ERR_MSG = 'Error occurred while retrieving the data from database: ' + ERROR_MESSAGE()

           RAISERROR (50001, @ERR_SEV, @ERR_STA,  @ERR_MSG) WITH NOWAIT

       END CATCH

       SELECT '…Contining with the procedure because I did not want to explicitly exit'

    GO

    drop Procedure dbo.xTestThrow

    go

    create Procedure dbo.xTestThrow

    as

    set nocount on

       DECLARE  @ERR_MSG   NVARCHAR(4000),

                @ERR_SEV   SMALLINT,

                @ERR_STA   SMALLINT

       BEGIN TRY

           SELECT 1/0 DivideByZero

       END TRY

       BEGIN CATCH

           SELECT  @ERR_SEV = ERROR_SEVERITY(),

                   @ERR_STA = ERROR_STATE(),

                   @ERR_MSG = 'Error occurred while retrieving the data from database: ' + ERROR_MESSAGE();

           THROW 50001, @ERR_MSG, @ERR_STA;

       END CATCH

       SELECT '…Contining with the procedure because I did not want to explicitly exit'

    GO

    exec xTestRaiserror

    exec xTestThrow

  5. Helen says:

    good point Bozola!

  6. Shah MOHAMOD says:

    Hi,

    I got a strange case where I was passing my custom error message, stored in a variable to the throw command. Strangely, on my client side (.Net application), I was getting a SqlException with an empty message. Passing the string error message directly to the throw command worked as expected. Can you please explain this behaviour ?

    Thanks

Skip to main content