Returning results as procedure parameters

Procedures can return results to the calling environment in the parameters to the procedure.

Within a procedure, parameters and variables can be assigned values using:

The SET statement
A SELECT statement with an INTO clause

Using the SET statement

The following somewhat artificial procedure returns a value in an OUT parameter that is assigned using a SET statement:

CREATE PROCEDURE greater (	IN a INT, 
									IN b INT, 
									OUT c INT)
BEGIN
	IF a > b THEN
		SET c = a;
	ELSE
		SET c = b;
	END IF ;
END

The preceding example is artificial: generally a function is easier to use than a procedure when only one result is required.

Using single-row SELECT statements

Single-row queries retrieve at most one row from the database. This type of query is achieved by a SELECT statement with an INTO clause. The INTO clause follows the select list and precedes the FROM clause. It contains a list of variables to receive the value for each select list item. There must be the same number of variables as there are select list items.

When a SELECT statement executes, 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, you must use cursors. 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.