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:

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/