Result set of SELECT statement in LOOP in MySQL Stored Procedure 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?
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