Advertisements
Home > Differences, SQL Server 2012 > New THROW statement in SQL Server 2012 (vs RAISERROR)

New THROW statement in SQL Server 2012 (vs RAISERROR)


Newly introduced THROW keyword 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: THROW will show the exact line where the exception was occurred, here the line number is 2. But RAISERROR will show the line number where the RAISERROR statement was executed i.e. Line 15, but not the actual exception position.

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.

Advertisements
  1. zorro-cool
    January 15, 2014 at 12:43 pm

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

    • January 15, 2014 at 12:48 pm

      Yes @zorro, msdn link [http://msdn.microsoft.com/en-us/library/ms178592.aspx] for RAISERROR mentions that “New applications should use THROW instead.”. So it looks like it might get deprecated in near future, or may be not.

  1. October 23, 2015 at 7:01 pm
  2. March 28, 2015 at 10:24 pm
  3. February 4, 2014 at 10:45 am
  4. January 3, 2014 at 1:58 pm
  5. January 24, 2013 at 6:33 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: