Listing 2: Outer Procedure usp_ insertOrder
CREATE PROCEDURE usp_insertOrder
    @product_id int,
    @quantity int,
    @order_id int OUTPUT
AS
/*1*/    DECLARE @price money, @return_status int
/*2*/    EXEC @return_status = sp_lookupPrice @product_id, @price OUTPUT
/*3*/    IF @return_status != 0
/*4*/       BEGIN
/*5*/	     IF @return_status = 10
/*6*/	        RAISERROR ('Invalid ID', 16, 1)
/*7*/	     ELSE
/*8*/	         IF @return_status = 11
/*9*/	         RAISERROR ('Missing price', 16, 1)
/*10*/	     ELSE
/*11*/	        RAISERROR ('Unknown error', 16, 1)
/*12*/	   	RETURN 99
/*13*/   	 END
/*14*/  INSERT Orders (product_id, quantity, unit_price, ext_price)
/*15*/    VALUES (@product_id, @quantity, @price, @quantity*@price)
/*16*/  IF @@error !=0
/*17*/    RETURN 12
/*18*/  SELECT @order_id = @@identity
/*19*/  RETURN 0