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(), 
   ERROR_MESSAGE(), 
   ERROR_LINE(); 
 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 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 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;