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 –
Insert few records in ‘SampleOrders’ table as a transaction, as shown below –
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
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)
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
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 –
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.
Now let’s execute the stored procedure and check the output of the procedure as shown below –
EXEC CheckOrderTotalAmt 10248