Hi,
I need to make a stored procedure or function that returns a set of rows. I've noted that in a stored procedure i can SELECT * FROM table with success. If i fetch rows in a loop and SELECT something, something_other FROM table once per loop execution, I only get one single result.
What I need to do is looping, doing some calculations and returning a rowset. What's the best way to do this? A temporary table? Stored functions?
Any help appreciated.
You can return multiple result sets but only if the client library supports it and the client is expecting it. Some don't, so using them will result in out-of-sequence errors.
You can certainly build a temporary table, select from it and drop it inside the procedure, that would be safe. Another option is to build a UNION select using a prepared SQL statement which returns all the rows you need and execute that. That's a bit messy.
It sounds like you're using a cursor inside the body of the stored procedure to accomplish your looping?
My first advise is: try to do your calculations in a single query without resorting to cursors. What's the calculation exactly?
If you really do need to use a cursor, then INSERT the results of each loop into a temporary table and then SELECT * from that table when you're done looping.