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
SELECTstatement 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?