we if any exception/error arises in the TRY block, we can do the follwoing:
1. provide alternate solution
2. raise error to the front-end app
3. Log the error message to one table or a flat file
the below code will raise an error to the client app
BEGIN TRY
DECLARE @MyInt INT;
-- Following statement will create Devide by Zero Error
SET @MyInt = 1/0;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
SELECT @ErrorMessage = ERROR_MESSAGE();
RAISERROR (@ErrorMessage, 16, 1);
END CATCH;
GO