Returning variable result sets from procedures

The RESULT clause is optional in procedures. Omitting the result clause allows you to write procedures that return different result sets, with different numbers or types of columns, depending on how they are executed.

If you do not use the variable result sets feature, you should use a RESULT clause to provide better performance, and to allow front-end tools to discern the columns and data types the procedure will produce without executing it.

For example, the following procedure returns two columns if the input variable is Y, but only one column otherwise:

CREATE PROCEDURE names( IN formal char(1))
BEGIN
	IF formal = 'y' THEN
		SELECT emp_lname, emp_fname 
		FROM employee
	ELSE
		SELECT emp_fname 
		FROM employee
	END IF
END

The use of variable result sets in procedures is subject to some limitations, depending on the interface used by the client application.