when an error occurs the processing in the TRY block stops and processing is then picked up in the CATCH block.

* Each TRY block is associated with only one CATCH block and must be followed immediately by an associated CATCH block.

* The CATCH block is executed only when any error occurs. If no errors, then the next statement after the END CATCH is executed.

* TRY-CATCH constructs can be nested.

* On ERROR, the control passes to the first statement in the associated CATCH block.

Here it is done with an example :

Step 1 : Created a stored procedure for a non-existent table - Temp

CREATE PROCEDURE usp_TestingException
as
BEGIN
SELECT * FROM Temp
END

Step 2 : Run the stored procedure using the below code, the error is sent to the CATCH block and an error message is returned. At this point processing can continue without a problem.

Example 1:

BEGIN TRY
EXECUTE usp_TestingException
END TRY

BEGIN CATCH
SELECT
Error_Number() as ErrorNumber,
Error_Severity() as ErrorSeverity,
Error_State() as ErrorState,
Error_procedure() as ErrorProcedure,
Error_Line() as ErrorLine,
Error_Message() as ErrorMessage
END CATCH

Example 2 :

BEGIN TRY
SELECT GETDATE()
SELECT 1/0
END
BEGIN CATCH
SELECT
Error_Number() as ErrorNumber,
Error_Severity() as ErrorSeverity,
Error_State() as ErrorState,
Error_procedure() as ErrorProcedure,
Error_Line() as ErrorLine,
Error_Message() as ErrorMessage
END CATCH