New Throw Statement in SQL Server 2012

In this blog we will learn about New Throw Statement in SQL Server 2012.

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.

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

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.