Unanswered: Returning resultsets from a stored procedure
With SQL Server, I can put a simple SELECT statement in a stored procedure, and it will return a resultset.
However, to get the same results with Informix, it looks like I need to make a list of datatypes in the RETURNING clause, and then select a row of data INTO a set of variables, and return these values one record at a time.
First, is this the only way to return a table-like resultset from a stored procedure, or is there an easier way that I missed?
Also, is there a way to return two resultsets from a single stored procedure?
Because the CREATE PROCEDURE blah RETURNING arg1, ... argn; syntax eplicitly specifies the number, data type, and order of return arguments, your options are really limited in temrs of the flexibility of the result set you pass back. You can return multiple sets that meet the same data layout as defined in your RETURNING clause (and the responsibility for identifying and applying decision criteria is then all yours), or you can return NULLs in all the buckets by using a RETURN; statement with no args. That's it- any attempt to return a set of args that don't fit those parameters will generate an Informix error.
Going back to the first part, if I understand correctly, you are wondering about having to select one row at a time and return it, and that's not necessarily the case. Using a FOREACH SELECT blah INTO local_blah loop, you can "RETURN arg1, ... argn WITH RESUME;", then end your FOREACH loop in the procedure, and this will send back a multi-row result set. I always return an empty row of NULLS (RETURN at the end of that, but can't recall if that's absolutely required or just good technique (or neither <g>).
There is a lot of documentation available online and in the Informix manuals, so I encourage you to jump in and get your feet wet. I hope this helps get you started.