TRY...CATCH in SQL Server

TRY...CATCH block is very efficient mechanism for error handling. In the previous versions of SQL Server @@Error was used, that required checking for error after every SQL statement.

The way TRY...CATCH works is similar to the way it works in any programming language i.e. if there is an error in the TRY block, the control is then passed on to the CATCH block, inside the CATCH block we can process the error the way we want for instance some of the scenarios can be...

1. If a code failed under TRY block we might want to roll back the entire operation. This roll back can be inside CATCH block

2. Or, we may want to send a notification to Administrator about the failure

3. Or, we may want to start a new operation all together, say on the 29th of every month some updates take place but if it is month of February and year is not a Leap Year then the job might fail and henceforth we might want to process this in CATCH block.

If there is no error, the control is passed to statement after END CATCH.

Why is it important to write a code in TRY...CATCH block: Say we have 2 SQL statement and error occurred while processing the second statement, SQL server would still commit the first transaction and therefore it is always recommended to use code under TRY...CATCH block.

Few Points to remember are:

Errors with Severity of 20 and higher are not handled by TRY...CATCH as they would cause the connection to be closed for that particular user

Errors with Severity 10 or less are also not handled by TRY...CATCH as are just the warnings

Any syntax error or type mismatch will also not be handled by TRY...CATCH.

This will be clear with some examples. We are using AdventureWorks for this.

begin try

    begin tran

        update sod set sod.UnitPrice=sod.UnitPrice/0

--This will give a divide by zero error

        from SalesLT.Product p inner join SalesLT.SalesOrderDetail sod

        ON p.ProductID =sod.ProductID where Weight is not null and Size in (44,58)

    

END Try

    begin catch

select 'Failed to Update' as ERRORSTATEMENT ,ERROR_NUMBER() ERRORNUMBER ,ERROR_SEVERITY() ERRORSEVERITY ,ERROR_STATE() ERRORSTATE    ,ERROR_PROCEDURE() ERRORPROCEDURE ,ERROR_LINE() ERRORLINE

    end catch

    commit tran

----------------------------------------------------------------------------

The same can be used for transaction rollback

begin try

    begin tran

        update sod set sod.UnitPrice=sod.UnitPrice/0

--This will give a divide by zero error

        from SalesLT.Product p inner join SalesLT.SalesOrderDetail sod

        ON p.ProductID =sod.ProductID where Weight is not null and Size in (44,58)

    

END Try

    begin catch

rollback tran

--rollback transaction and send notification to Admin

exec msdb.dbo.sp_send_dbmail

@profile_name='ProfileName',

@recipients='Admin@example.com',

@body='An error occurred due to which transaction was rolled back',

@subject='Error on Server'

    end catch

    commit tran

 

Tags: , , , , ,

SQL Server