How does MySQL return a result set from a stored procedure?

Question

I'm trying to get my head around stored procedures for use in a web application. It seems to me that whatever the last statement is in a MySQL stored procedure is seems to be what gets treated as that procedure's result set. Unfortunately though, it seems there are references in the MySQL docs that say a procedure can return multiple result sets. How is this behavior triggered? How can I tell the MySQL server that I explicitly want only one result set returned?

(For instance, I have a query that does a SELECT and a few inserts. I don't want to tell clients about the inserts, but I do want the client to be given the SELECT's result set....)

Answer 1

Each SELECT statement that does not insert into a table or a variable will produce a result set.

If you want your stored procedure to return only one result set, make sure that you only have one SELECT statement. If you have other SELECT statements, make sure that they insert results into a table or variable.

UPDATE
Here are examples of stored procedures.

This stored procedure would return one result set:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
 DECLARE local_variable_name INT;
 SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;
 SELECT * FROM table_1;
END;;
DELIMITER ;

This stored procedure would return two result sets:

DELIMITER ;;
CREATE DEFINER=CURRENT_USER PROCEDURE stored_procedure_name()
BEGIN
 DECLARE local_variable_name INT;
 SELECT column_name FROM table_1 LIMIT 1 INTO local_variable_name;
 SELECT * FROM table_1;
 SELECT * FROM table_2;
END;;
DELIMITER ;

• Billy ONeal: Ah, so if it goes into a table or variable, it doesn't get included as a result? How does one access that; using e.g. SELECT INTO?
• dabest1: Yes to first question. I'm not clear on your second question. If I want to save a value into a local variable from within the stored procedure, I run something like this: SELECT column_name LIMIT 1 INTO local_variable_name;.
• dabest1: Sample query above should have been: SELECT column_name FROM table LIMIT 1 INTO local_variable_name;.
• 王奕然: if i want to get variable after call stored_procedure_name from another stored procedure,i can't set result = call stored_procedure_name();,how to solve it?if i use cursor,DECLARE c_dept CURSOR FOR call get_info_user_visitstatistics() syntax error