MySQL Stored Procedure Error – Differences Between MySQL 5.0 and 5.5

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 ;

If all your data is InnoDB, I have a surprise for you.

It is a little-know fact, but there are some occasions when an older version of MySQL/InnoDB can outrun/outgun a newer of MySQL/InnoDB. How can that be possible?

I wrote about this in DBA StackExchange before

• Why mysql 5.5 slower than 5.1 (linux,using mysqlslap) (Nov 24, 2011)
• Query runs a long time in some newer MySQL versions (Oct 05, 2011)
• Multi cores and MySQL Performance (Sep 20, 2011)
• How do I properly perform a MySQL bake-off? (Jun 19, 2011)

You will have to perform some due diligence in configuring MySQL to harness more threading and more CPUs.