TRANSACTION and Error Handling

Transactions are very useful when you are not sure if there could be a error or not. And if there is a error, the set of sql statements need not be executed or needs to be rolled back.In this case we can TRANSACTIONS that help us to ROLLBACK if there was any error while executing the sql statements.

The below example also shows simple error handling. If there is any error while updating the table then control is passed on to the CATCH block and the TRANSACTION is rolled back.

USE [PUBS]
GO

DECLARE @iError int

BEGIN TRANSACTION

    UPDATE table
    SET column = 'value'
    WHERE column = 'condtion'
   
    select @iError = @@ERROR
    IF (@iError <> 0) GOTO CATCH
   
COMMIT TRANSACTION

CATCH:
IF(@iError <> 0)
BEGIN
    PRINT 'Error message'
    ROLLBACK TRANSACTION
END