Contents Index Returning results as procedure parameters Returning multiple result sets from procedures

ASA SQL User's Guide
  Using Procedures, Triggers, and Batches
    Returning results from procedures

Returning result sets from procedures


Result sets allow a procedure to return more than one row of results to the calling environment.

The following procedure returns a list of customers who have placed orders, together with the total value of the orders placed. The procedure does not list customers who have not placed orders.

CREATE PROCEDURE ListCustomerValue ()
RESULT ("Company" CHAR(36), "Value" INT)
BEGIN
   SELECT company_name,
      CAST( sum(   sales_order_items.quantity *
                  product.unit_price)
                  AS INTEGER ) AS value
   FROM customer
      INNER JOIN sales_order
      INNER JOIN sales_order_items
      INNER JOIN product
   GROUP BY company_name
   ORDER BY value DESC;
END
Company Value
Chadwicks 8076
Overland Army Navy 8064
Martins Landing 6888
Sterling & Co. 6804
Carmel Industries 6780
... ...
Notes 

Contents Index Returning results as procedure parameters Returning multiple result sets from procedures