THROW; Re-Throw Exception From TRY CATCH Block In Denali (MSSQL2012)

Denali Has come up with the Feature of Re-Throwing Exceptions which is very important and required in certain situations.

• THROW can be used with in TRY Catch Block.
• Statement Before THROW Must be End With SemiColon ;
• Benefit Over RAISERROR
• Error Number Can Be Passed Even If Error Number Is Not Defined In Sys.Messages

Working For RAISERROR And THROW

Lets See Following Example For RAISERROR

Begin Try
   Select 1/0
End Try
Begin Catch
   DECLARE @ErrorMessage NVARCHAR(4000);
   DECLARE @ErrorSeverity INT;
   DECLARE @ErrorState INT;
   SELECT @ErrorMessage = ERROR_MESSAGE(),
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE();
   RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
End Catch

Output :

(0 row(s) affected)
Msg 50000, Level 16, State 1, Line 11
Divide by zero error encountered.

Now Let’s See THROW

Begin Try
   Select 1/0
End Try
Begin Catch
   THROW
End Catch

Output :

(0 row(s) affected)
Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.

---

RAISE CUSTOM Message Without System Error Number.

Begin
   Select 1/0
End Try
Begin Catch
   RAISERROR (50005,10,1,'Exception Occured In Code');
End Catch

Output;

(0 row(s) affected)
Msg 18054, Level 16, State 1, Line 5
Error 50005, severity 10, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.

This Is Because RAISERROR Only Accept Those Message_ID Which Exists in Sys.Messages Table.

Now Lets Check With THROW

Begin Try
   Select 1/0
End Try
Begin Catch
   THROW 50005,'Exception Occured In Code',1
End Catch

Output:

(0 row(s) affected)
Msg 50005, Level 16, State 1, Line 5
Exception Occured In Code

You can Clearly See THROW Is More Flexible in Use and You can raise error with your own message number and Messages.