SQL Server–Error Handling using Try Catch Block

In this post, we will see how to handle errors in SQL Server 2005 and 2008. In SQL Server 2005, Microsoft has introduced a new construct to handle errors in SQL Server that is ‘TRY – CATCH’. It is similar to .NET ‘Try – Catch’ block for handling the exceptions. When an error is caught in a T-SQL statement which is inside the ‘TRY’ block, the control is automatically transferred to the ‘CATCH’ block.

We can then process the error in the ‘CATCH’ block.

So let’s see some examples of the TRY CATCH block for handling the errors in SQL Server. First let’s create two tables with a relationship between them as shown below –

Now let’s insert three records in ‘SampleCustomers’ table as shown below –

sqlserver-try-catch-table1

Insert few records in ‘SampleOrders’ table as a transaction, as shown below –

sqlserver-try-catch-transaction

Now select the records from both the tables as shown below –

If you observe, we are using ‘XACT_ABORT ON’ statement before starting the transaction. Because of this statement set to ‘ON’, if the T-SQL statement raise the error, the entire transaction will be roll backed. So in the data above, data for ‘SampleOrders’ did not get inserted.

Now let’s try the same transaction by setting the ‘XACT_ABORT’ statement to ‘OFF’. The transaction will roll back the statements which has errors, but the other statements will get committed as shown in the following output –

SET XACT_ABORT OFF
BEGIN TRY
    BEGIN TRAN
        INSERT INTO SampleOrders VALUES(100,GETDATE()-1,GETDATE()+5,1)
        INSERT INTO SampleOrders VALUES(101,GETDATE()-1,GETDATE()+5,4)
        INSERT INTO SampleOrders VALUES(102,GETDATE()-1,GETDATE()+5,2)
    COMMIT TRAN
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;
        ROLLBACK
END CATCH

Now if you execute the above transaction which uses the ‘TRY-CATCH’ block, when an error occurs in a T-SQL statement, it moves the control from ‘TRY’ block to ‘CATCH’ block where we are rolling back the complete transaction. The output will show the error information by using couple of in-build error functions as shown below –

clip_image002

Now let’s write a stored procedure which will raise an error and we will capture the user defined error by using ‘TRY-CATCH’ block. The scenario is, if the total amount of order no. is not exceeding 500, the order will not be processed now.

sqlserver-try-catch-demo

Now let’s execute the stored procedure and check the output of the procedure as shown below –

EXEC CheckOrderTotalAmt 10248

clip_image001

SQL Server–Error Handling using Try Catch Block

No comments: