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

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Advanced SQL Engine
Teradata Database
Release Number
January 2021
Content Type
Programming Reference
Publication ID
English (United States)
Last Update

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

    CREATE PROCEDURE salesadjust(IN item    INTEGER, 
                                 IN numsold INTEGER)
        DECLARE price DECIMAL(8,2);
        SELECT item_price INTO price 
        FROM pricetbl 
        WHERE item = pricetbl.item_no;
          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;

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.