Sybase 12.4.2 manual Show the number of orders placed by the customer with ID, 248

Returning results from procedures

When a SELECT statement is executed, the server retrieves the results of the SELECT statement and places the results in the variables. If the query results contain more than one row, the server returns an error. For queries returning more than one row, cursors must be used. For information about returning more than one row from a procedure, see “Returning result sets from procedures”.

If the query results in no rows being selected, a

row not found

warning is returned.

The following procedure returns the results of a single-row SELECT statement in the procedure parameters.

To return the number of orders placed by a given customer, type the following:

CREATE PROCEDURE OrderCount (IN customer_ID INT,

OUT Orders INT)

BEGIN

SELECT COUNT(DBA.sales_order.id)

INTO Orders

FROM DBA.customer

KEY LEFT OUTER JOIN DBA.sales_order

WHERE DBA.customer.id = customer_ID;

END

	You can test this procedure in DBISQL using the following statements, which
	show the number of orders placed by the customer with ID 102:
	CREATE VARIABLE orders INT;
	CALL OrderCount ( 102, orders );
	SELECT orders;
Notes	• The customer_ID parameter is declared as an IN parameter. This
	parameter holds the customer ID that is passed in to the procedure.
	• The Orders parameter is declared as an OUT parameter. It holds the value
	of the orders variable that is returned to the calling environment.
	• No DECLARE statement is required for the Orders variable, as it is
	declared in the procedure argument list.
	• The SELECT statement returns a single row and places it into the variable
	Orders.

248