Database Simplified Headline Animator

Database Simplified

Saturday 1 October 2011

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.

No comments:

Post a Comment