Filter
Top Products
CHAPTER 6 Using Procedures and Batches
249
Returning result sets from procedures
If a procedure returns more than one row of results to the calling environment,
it does so using result sets.
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" NUMERIC(14,2))
BEGIN
SELECT company_name,
CAST( sum(sales_order_items.quantity *
product.unit_price)
AS NUMERIC(14,2)) 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
• Type the following:
CALL ListCustomerValue ()
Notes
• The number of variables in the RESULT list must match the number of the
SELECT list items. Automatic data type conversion is carried out where
possible if data types do not match.
• The RESULT clause is part of the CREATE PROCEDURE statement, and
is not followed by a command delimiter.
• The names of the SELECT list items do not need to match those of the
RESULT list.
Company Value
Chadwicks 8076
Overland Army Navy 8064
Martins Landing 6888
Sterling & Co. 6804
Carmel Industries 6780
... ...