You can see there is a need for error handling by producing an error. The following code tries to insert an order and a detail row for this order:

EXEC dbo.InsertSimpleOrder 
 @OrderId = 6, @OrderDate = '20160706', @Customer = N'CustE'; 
EXEC dbo.InsertSimpleOrderDetail 
 @OrderId = 6, @ProductId = 2, @Quantity = 0; 

In SQL Server Management Studio, you can see that an error occurred. You should get a message that the error 547 occurred, that the INSERT statement conflicted with the CHECK constraint. If you remember, in order details, only rows where the value for the quantity is not equal to zero are allowed. The error occurred in the second statement, in the call of the procedure that inserts an order detail. The procedure that inserted an order was executed without an error. Therefore, an order with an ID equal to six must be in the dbo.SimpleOrders table. The following code tries to insert order 6 again:

EXEC dbo.InsertSimpleOrder 
 @OrderId = 6, @OrderDate = '20160706', @Customer = N'CustE'; 

Of course, another error occurred. This time it should be error 2627, a violation of the PRIMARY KEY constraint. The values of the OrderId column must be unique. Let's check the state of the data after these successful and unsuccessful inserts:

SELECT o.OrderId, o.OrderDate, o.Customer, 
  od.ProductId, od.Quantity 
FROM dbo.SimpleOrderDetails AS od 
  RIGHT OUTER JOIN dbo.SimpleOrders AS o 
    ON od.OrderId = o.OrderId 
WHERE o.OrderId > 5 
ORDER BY o.OrderId, od.ProductId; 

The previous query checks only orders and their associated details where the order ID value is greater than five. The query returns the following result set:

OrderId     OrderDate  Customer ProductId   Quantity
----------- ---------- -------- ----------- --------
6           2016-07-06 CustE    NULL        NULL

You can see that only the first insert of the order with the ID 6 succeeded. The second insert of an order with the same ID and the insert of the detail row for the order six did not succeed.

You start handling errors by enclosing the statements in the batch you are executing in the BEGIN TRY...END TRY block. You can catch the errors in the BEGIN CATCH...END CATCH block. The BEGIN CATCH statement must be immediately after the END TRY statement. The control of the execution is passed from the try part to the catch part immediately after the first error occurs.

In the catch part, you can decide how to handle the errors. If you want to log the data about the error or inform an end user about the details of the error, the following functions might be very handy:

The following code uses the try...catch block to handle possible errors in the batch of the statements, and returns the information of the error using the preceding mentioned functions. Note that the error happens in the first statement of the batch:

BEGIN TRY 
 EXEC dbo.InsertSimpleOrder 
  @OrderId = 6, @OrderDate = '20160706', @Customer = N'CustF'; 
 EXEC dbo.InsertSimpleOrderDetail 
  @OrderId = 6, @ProductId = 2, @Quantity = 5; 
END TRY 
BEGIN CATCH 
 SELECT ERROR_NUMBER() AS ErrorNumber, 
   ERROR_MESSAGE() AS ErrorMessage, 
   ERROR_LINE() as ErrorLine; 
END CATCH 

There was a violation of the PRIMARY KEY constraint again, because the code tried to insert an order with an ID of six again. The second statement would succeed if you executed it in its own batch, without error handling. However, because of the error handling, the control was passed to the catch block immediately after the error in the first statement, and the second statement never executed. You can check the data with the following query:

SELECT o.OrderId, o.OrderDate, o.Customer, 
  od.ProductId, od.Quantity 
FROM dbo.SimpleOrderDetails AS od 
  RIGHT OUTER JOIN dbo.SimpleOrders AS o 
    ON od.OrderId = o.OrderId 
WHERE o.OrderId > 5 
ORDER BY o.OrderId, od.ProductId; 

The result set should be the same as the results set of the last check of the orders with an ID greater than five—a single order without details. The following code produces an error in the second statement:

BEGIN TRY 
 EXEC dbo.InsertSimpleOrder 
  @OrderId = 7, @OrderDate = '20160706', @Customer = N'CustF'; 
 EXEC dbo.InsertSimpleOrderDetail 
  @OrderId = 7, @ProductId = 2, @Quantity = 0; 
END TRY 
BEGIN CATCH 
 SELECT ERROR_NUMBER() AS ErrorNumber, 
   ERROR_MESSAGE() AS ErrorMessage, 
   ERROR_LINE() as ErrorLine; 
END CATCH 

You can see that the insert of the order detail violates the CHECK constraint for the quantity. If you check the data with the same query as the last two times again, you will see that there are orders with an ID of six and seven in the data, both without order details.

Your business logic might request that the insert of the first statement fails when the second statement fails. You might need to repeal the changes of the first statement on the failure of the second statement. You can define that a batch of statements executes as a unit by using transactions. The following code shows how to use transactions. Again, the second statement in the batch in the try block is the one that produces an error:

BEGIN TRY 
 BEGIN TRANSACTION 
  EXEC dbo.InsertSimpleOrder 
   @OrderId = 8, @OrderDate = '20160706', @Customer = N'CustG'; 
  EXEC dbo.InsertSimpleOrderDetail 
   @OrderId = 8, @ProductId = 2, @Quantity = 0; 
 COMMIT TRANSACTION 
END TRY 
BEGIN CATCH 
SELECT ERROR_NUMBER() AS ErrorNumber, 
   ERROR_MESSAGE() AS ErrorMessage, 
   ERROR_LINE() as ErrorLine; 
 IF XACT_STATE() <> 0 
    ROLLBACK TRANSACTION; 
END CATCH 

You can check the data again:

SELECT o.OrderId, o.OrderDate, o.Customer, 
  od.ProductId, od.Quantity 
FROM dbo.SimpleOrderDetails AS od 
  RIGHT OUTER JOIN dbo.SimpleOrders AS o 
    ON od.OrderId = o.OrderId 
WHERE o.OrderId > 5 
ORDER BY o.OrderId, od.ProductId; 

Here is the result of the check:

OrderId     OrderDate  Customer ProductId   Quantity
----------- ---------- -------- ----------- -----------
6           2016-07-06 CustE    NULL        NULL
7           2016-07-06 CustF    NULL        NULL

You can see that the order with the ID 8 does not exist in your data. Because the insert of the detail row for this order failed, the insert of the order was rolled back as well. Note that in the catch block, the XACT_STATE() function was used to check whether the transaction still exists. If the transaction was rolled back automatically by SQL Server, then the ROLLBACK TRANSACTION would produce a new error.

The following code drops the objects (incorrect order, due to object constraints) created for the explanation of the DDL and DML statements, programmatic objects, error handling, and transactions:

DROP FUNCTION dbo.Top2OrderDetails; 
DROP VIEW dbo.OrdersWithoutDetails; 
DROP PROCEDURE dbo.InsertSimpleOrderDetail; 
DROP PROCEDURE dbo.InsertSimpleOrder; 
DROP TABLE dbo.SimpleOrderDetails; 
DROP TABLE dbo.SimpleOrders;