Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    2

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

    Thanks,

    Shawn

  2. #2
    Join Date
    Dec 2002
    Location
    Portland, OR, USA
    Posts
    26

    You're on the right track...

    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

  3. #3
    Join Date
    Mar 2004
    Posts
    8

    Arrow Re: Returning resultsets from a stored procedure

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •