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 has happened. You should get a message that error 547 has 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 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 OrderID 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 order 6 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:

• ERROR_NUMBER(): This function returns the number of the error.
• ERROR_SEVERITY(): This function returns the severity level. The severity of the error indicates the type of problem encountered. Severity levels 11 to 16 can be corrected by the user.
• ERROR_STATE(): This function returns the error state number. Error state gives more details about a specific error. You might want to use this number together with the error number to search the Microsoft knowledge base for the specific details of the error you encountered.
• ERROR_PROCEDURE(): This returns the name of the stored procedure or trigger where the error occurred, or NULL if the error did not occur within a stored procedure or trigger.
• ERROR_LINE(): This returns the line number at which the error occurred. This might be the line number in a routine if the error occurred within a stored procedure or trigger, or the line number in the batch.
• ERROR_MESSAGE(): This function returns the text of the error message.

The following code uses the TRY...CATCH block to handle possible errors in the batch of statements, and returns the information about the error using the aforementioned 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(), 
   ERROR_MESSAGE(), 
   ERROR_LINE(); 
END CATCH 

There was a violation of the PRIMARY KEY constraint again, because the code tried to insert an order with ID 6 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(), 
   ERROR_MESSAGE(), 
   ERROR_LINE(); 
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 last time again, you would see that there are orders with ID 6 and 7 in the data, both without order details.