SQL Server – Using TRY/CATCH

In the following examples, we will see how to implement error handling in SQL Server using TRY/CATCH

CREATE TABLE dbo.Products(
productid INT CONSTRAINT PK_Products_productid PRIMARY KEY,
productname VARCHAR(30));
 
CREATE TABLE dbo.Orders(
orderid INT CONSTRAINT PK_Orders_orderid PRIMARY KEY,
orderdate DATETIME);
 
CREATE TABLE dbo.OrderDetails(
orderid INT CONSTRAINT FK_OrderDetails_Order FOREIGN KEY REFERENCES dbo.Orders,
productid INT CONSTRAINT FK_OrderDetails_Product FOREIGN KEY REFERENCES dbo.Products);
 
INSERT INTO dbo.Products VALUES
(1, 'Product A'),
(2, 'Product B'),
(3, 'Product C');
 
INSERT INTO dbo.Orders VALUES
(1, CURRENT_TIMESTAMP);
 
INSERT INTO dbo.OrderDetails VALUES
(1, 1),
(1, 2),
(1, 3);
 

Violation of Primary Key

BEGIN TRY
   INSERT INTO dbo.Products VALUES (3, 'Product D');
END TRY
BEGIN CATCH
   SELECT
   'ERROR NUMBER: ' + CAST(ERROR_NUMBER() as varchar(8000)) +
   ' ERROR MESSAGE: ' + ERROR_MESSAGE() AS 'Error description';
END CATCH
ERROR NUMBER: 2627 ERROR MESSAGE: Violation of PRIMARY KEY constraint 'PK_Products_productid'. Cannot insert duplicate key in object 'dbo.Products'. The duplicate key value is (3).

ERROR NUMBER: 2627 ERROR MESSAGE: Violation of PRIMARY KEY constraint 'PK_Products_productid'. Cannot insert duplicate key in object 'dbo.Products'. The duplicate key value is (3).

Violation of Foreign Key

BEGIN TRY
   INSERT INTO dbo.OrderDetails (orderid,productid) VALUES (1,4)
END TRY
BEGIN CATCH
   SELECT
   'ERROR NUMBER: ' + CAST(ERROR_NUMBER() as varchar(8000)) +
   ' ERROR MESSAGE: ' + ERROR_MESSAGE() AS 'Error description';
END CATCH

ERROR NUMBER: 547 ERROR MESSAGE: The INSERT statement conflicted with the FOREIGN KEY constraint “FR_OrderDetails_Product”. The conflict occurred in database “TSQL2012”, table “dbo.Products”, column 'productid'.

Dropping sample tables

DROP TABLE dbo.OrderDetails;
DROP TABLE dbo.Products;
DROP TABLE dbo.Orders;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s