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?

Thanks,

Shawn

I'll respond to your last question first.

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.

Regards,
Joe

As a simple example of what Astrue said:

CREATE PROCEDURE TEST()
RETURNING CHAR(64), INTEGER, INTEGER, CHAR(64);

DEFINE LsField1,
LsField4 CHAR(64);
DEFINE LsField2,
LsField3 INTEGER;

FOREACH
SELECT Field1, Field2, Field3, Field4
INTO LsField1, LnField2, LnField3, LsField4
FROM Historico
WHERE Nombre_Usuario=Usuario
ORDER BY Grupo ASC

RETURN LsField1, LdField2, LdField3, LsField4
WITH RESUME;

END FOREACH
END PROCEDURE

Greetings