1- TRY Block: This is where you place the code that might throw an error. If an error occurs within the TRY block, the control is passed to the CATCH block.
BEGIN TRY
-- SQL statements that might cause an error
END TRY
2- CATCH Block: In this block, you can specify how to handle the error that occurred in the TRY block. You can log the error, raise a custom error, or perform any necessary cleanup operations.
BEGIN CATCH
-- Error handling code
END CATCH
3- Error Functions: Within the CATCH block, you have access to several functions like ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), and ERROR_PROCEDURE() to retrieve detailed information about the error.
Benefits of TRY...CATCH:
- Robust Error Handling: It helps in writing more robust code by providing a structured way to handle errors.
- Transaction Control: TRY...CATCH can be used to control transactions effectively. You can rollback a transaction in the CATCH block if an error occurs.
- Logging and Notification: Errors can be logged or notifications can be sent to alert administrators about critical issues.
BEGIN TRY
-- Simulating a divide by zero error
SELECT 1/0
END TRY
BEGIN CATCH
PRINT 'An error occurred: ' + ERROR_MESSAGE()
END CATCH
For Example , there is a procedure for the Insert and update data :
CREATE PROCEDURE InsertAndUpdateData
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
-- Insert data into Table1
INSERT INTO Table1 (Column1, Column2)
VALUES ('Value1', 'Value2');
-- Update data in Table2
UPDATE Table2
SET Column3 = 'NewValue'
WHERE Column4 = 'Condition';
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
-- Error handling code here
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END