次の方法で共有

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: https://sqlwithmanoj.wordpress.com/2012/12/20/new-throw-statement-in-sql-server-2012-vs-raiserror/

Comments

  • Anonymous
    January 14, 2014
    Interesting post! Thank you! Does the last note mean that Microsoft intend to make the raiserror function deprecated in the future?

  • Anonymous
    January 15, 2014
    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.

  • Anonymous
    July 14, 2014
    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.

  • Anonymous
    October 22, 2014
    " 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

  • Anonymous
    February 12, 2015
    good point Bozola!

  • Anonymous
    February 24, 2016
    Great post!!

  • Anonymous
    June 27, 2016
    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