New THROW statement in SQL Server 2012 (vs RAISERROR)

New THROW statement in SQL Server 2012 (vs RAISERROR)

Rate This
  • Comments 2

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/

Leave a Comment
  • Please add 1 and 4 and type the answer here:
  • Post
  • Interesting post! Thank you! Does the last note mean that Microsoft intend to make the raiserror function deprecated in the future?

  • 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.

Page 1 of 1 (2 items)