Try Catch - Exception Handling

·         A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.

·         A TRY…CATCH construct cannot span two BEGIN…END blocks of Transact-SQL statements and cannot span an IF…ELSE construct.

·         If there are no errors in the code that is enclosed in a TRY block, when the last statement in the TRY block has finished running, control passes to the statement immediately after the associated END CATCH statement. If there is an error in the code that is enclosed in a TRY block, control passes to the first statement in the associated CATCH block. If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed back to the statement that called the stored procedure or fired the trigger.

·         When the code in the CATCH block finishes, control passes to the statement immediately after the END CATCH statement. Errors trapped by a CATCH block are not returned to the calling application. If any part of the error information must be returned to the application, the code in the CATCH block must do so by using mechanisms such as SELECT result sets or the RAISERROR and PRINT statements.

·         TRY…CATCH constructs can be nested. Either a TRY block or a CATCH block can contain nested TRY…CATCH constructs. For example, a CATCH block can contain an embedded 

·         TRY…CATCH construct to handle errors encountered by the CATCH code.

·         Errors encountered in a CATCH block are treated like errors generated anywhere else. If the CATCH block contains a nested TRY…CATCH construct, any error in the nested TRY block will pass control to the nested CATCH block. If there is no nested TRY…CATCH construct, the error is passed back to the caller.

·         If you use raiseerror, and if there is an error in the inner try catch block, control will go to main catch block and executes that code

·         If you use throw, if these is an error in the inner try catch block, control will go to main catch block and executes that code, along with inner catch block code

·         If there is NO try catch block in the procedure, it will go try catch block of the calling code

·         If there is try catch block in the procedure, it will go to next line in the calling code