Mysql – Result set of SELECT statement in LOOP in MySQL Stored Procedure

MySQLstored-procedures

I have the following SP:

CREATE PROCEDURE vessels_near_anchorage_test() 
   BEGIN 
      DECLARE arg0 VARCHAR(255);
      DECLARE arg1 FLOAT;
      DECLARE arg2 FLOAT;
      DECLARE arg3 FLOAT;
      DECLARE cur1 CURSOR FOR SELECT field1, field2 FROM table1;
      OPEN cur1; 
      read_loop: LOOP
           FETCH cur1 INTO arg0, arg1, arg2, arg3;
           SELECT * FROM table2 WHERE c1=arg0 AND c2=arg1;
      END LOOP;
      CLOSE cur1;
END;

When I try to result set from this SP in the application server, I get DataError: 1329 (02000): No data - zero rows fetched, selected, or processed. I want to understand how the SP is really being executed and what is causing this error.

In the SP above, the SELECT statement is inside the loop. It returns rows in some iterations while there may be no result in some iterations. When I run this in PhpMyAdmin, I get 850 rows, but it's entirely possible that the last iteration does not return any row.

When a SELECT statement is executed in a loop like that, can I combine results from all iterations and output as a single result set available by the time the loop completes?
For the application, is it only the result set from the last iteration in the loop made available which in this case has no rows?

Best Answer

CURSORs are rarely worth using. Try to get rid of them. In your example:

SELECT b.*
    FROM table2 AS b
    JOIN table1 AS a  ON b.c1 = a.field1
                     AND b.c2 = a.field2

Anyway, when you execute multiple SELECTs inside a Stored Proc, you get "multiple resultsets". Some interfaces either can't handle such, or need different coding.

And you had an error: The cursor SELECT fetched 2 columns but the FETCH expected 4.

Coding questions should be addressed to stackoverflow.com

Related Solutions

Mysql – How does MySQL return a result set from a stored procedure

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 ;

MySQL Stored Procedure – Loop Through Table and Delete Rows

There are two possible solutions:

set @z to null in the very beginning of your loop (prior to SET @sql_text2 = concat('....)

or instead of

SELECT id INTO @z FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1;

use this:

SET @z = (SELECT id FROM table_name WHERE id >= 7000 ORDER BY id LIMIT 1000,1);

Explanation:

When no row is found, the variable is unchanged from its previous value... so it's not that you have to "set it to null before you use it," it's that you have to reset it to null before you use it again, if you are doing something like SELECT ... INTO that won't reset the value if nothing is found.
see: https://dba.stackexchange.com/a/35207/12923

Best Answer

Related Solutions

Mysql – How does MySQL return a result set from a stored procedure

MySQL Stored Procedure – Loop Through Table and Delete Rows

Related Question