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.

1 comment:

  1. By 1970, the economies of most Western countries had slowed and employment prices have been rising. With the 60’s, having provided the agency expertise foundation that was needed, CNC took off and started steadily displacing older applied sciences corresponding to hydraulic tracers and handbook machining. CAD got here into its personal and started rapidly replacing paper drawings and draftsmen in the course of the 60’s. By 1970, CAD Baby Mittens was a decent sized trade with players like Intergraph and Computervision, each of whom I consulted for back in my college days. The manufacturing trade can be very rewarding, and CNC is one of those of|a type of} areas where you can to|you presumably can} never learn every little thing. CAD packages used to create drawings and 3D fashions of the components we want to machine.

    ReplyDelete