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 –
USE master
GO
CREATE TABLE SampleCustomers
(
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(30),
City VARCHAR(20)
)
CREATE TABLE SampleOrders
(
OrderID INT PRIMARY KEY,
OrderDate DATETIME,
RequiredDate DATETIME,
CustomerID INT REFERENCES SampleCustomers(CustomerID)
)
Now let’s insert three records in ‘SampleCustomers’ table as shown below –
INSERT INTO SampleCustomers VALUES(1,'PRAVIN','AP')
INSERT INTO SampleCustomers VALUES(2,'YASH','MUMBAI')
INSERT INTO SampleCustomers VALUES(3,'SAM','DELHI')
Insert few records in ‘SampleOrders’ table as a transaction, as shown below –
SET XACT_ABORT ON
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
Now select the records from both the tables as shown below –
Select * from SampleCustomers
Select * from SampleOrders
Now select the records from both the tables as shown below –
CustomerID CustomerName City
1 PRAVIN AP
2 YASH MUMBAI
3 SAM DELHI
OrderID OrderDate RequiredDate CustomerID
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 –
CustomerID CustomerName City
1 PRAVIN AP
2 YASH MUMBAI
3 SAM DELHI
OrderID OrderDate RequiredDate CustomerID
100 2013-11-18 19:29:02.333 2013-11-24 19:29:02.333 1
102 2013-11-18 19:29:02.343 2013-11-24 19:29:02.343 2
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 –
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
2627 14 1 NULL 4 Violation of PRIMARY KEY...
