Example: Creating an SQL Procedure with a Multistatement Request - Advanced SQL Engine - Teradata Database

The following procedure executes the statements in the multistatement request in parallel:

    CREATE PROCEDURE salesadjust(IN item    INTEGER, 
                                 IN numsold INTEGER)
      BEGIN
        DECLARE price DECIMAL(8,2);
        SELECT item_price INTO price 
        FROM pricetbl 
        WHERE item = pricetbl.item_no;
        BEGIN REQUEST
          UPDATE sales_summary
          SET total_sales = total_sales + price * numsold
          WHERE item = sales_summary.item_no
          ;UPDATE inventory
          SET item_no = item_no - numsold
          WHERE item = inventory.item_no;
        END REQUEST;
      END;

The stages in the process followed by this procedure are as follows:

1. The price of an item is retrieved from pricetbl.
2. The total sales amount is computed by multiplying the total price by the number of items sold.
3. Two update requests are dispatched to the AMPs in parallel:

   The first updates a total_sales summary table that keeps track of the cash made on all items sold.

   The second adjusts inventory to reflect the remaining quantity stocked for that particular item.