MySQL Stored Procedure Programming

Using Unbounded SELECT Statements

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 INTO 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—SHOW, EXPLAIN, DESC, and so on—can also be included within the stored procedure.

We have used unbounded SELECT 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.

Example 5-20. Using unbounded SELECTs to return data to the calling program

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 | +-------------------+----------+ ...