MySQL stored procedures (but not functions) can return
result sets to the calling program (though not, unfortunately,
directly to another stored procedure). A result set is returned from a
stored procedure whenever a SQL statement that returns a result set is
not associated with either an
clause or a cursor. We call these SQL statements
unbounded. Such SQL statements will usually be
SELECT statements, although other
statements that return result sets—
DESC, and so on—can also be included within
the stored procedure.
We have used unbounded
statements throughout many of our examples in order to return
information about stored procedure execution. You’ll most likely do
the same either for debugging purposes or to return some useful status
information to the user or calling program. Example 5-20 shows an example of
a stored procedure that uses this feature to return a list of
employees within a specific department.
CREATE PROCEDURE emps_in_dept(in_department_id INT) BEGIN SELECT department_name, location FROM departments WHERE department_id=in_department_id; SELECT employee_id,surname,firstname FROM employees WHERE department_id=in_department_id; END;
When run, the stored procedure from Example 5-20 produces the following output:
mysql> CALL emps_in_dept(31) // +-------------------+----------+ | department_name | location | +-------------------+----------+ ...