Sybase 12.4.2 manual Returning result sets from procedures, 249

CHAPTER 6 Using Procedures and Batches

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 ()

Company	Value
Chadwicks	8076
Overland Army Navy	8064
Martins Landing	6888
Sterling & Co.	6804
Carmel Industries	6780
...	...

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.

249