I'm writing a stored procedure that will perform a SELECT, loop through it with a cursor, do some mildly complicated processing, and then return some of the rows in a modified form.
One way to do this is to create a temporary table, insert the results of the processing there, and then finish the procedure with a select * from temp_table;
I'm wondering if I can skip that part and just stream the computed rows directly to the client, without involving the overhead of temporary storage.
Every unbounded SELECT within a stored procedure writes a separate result-set to the wire. The only way to combine them into a single result-set is to use a temporary table, and then SELECT from it when you're done.
Otherwise, the multiple result sets must be iterated by the client by calling more_results, which tells you whether more results are available. It returns false when your procedure finishes.
The mechanism MySQL uses to actually write these multiple result sets is very lightweight, so it depends on whether this works for what you're trying to do, whether this is useful in your application.