Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2004
    Posts
    6

    Question Unanswered: Stored Procs Returning output from Select Queries

    Informix 9.x, is there a way i could directly make the stored procedures return Results from Stored Procedures like in SQL or Oracle.

    For Example in SQL Server i can say something like:

    CREATE PROCEDURE au_info
    @lastname varchar(40),
    @firstname varchar(20)
    AS
    SELECT au_lname, au_fname, title, pub_name
    FROM authors a INNER JOIN titleauthor ta
    ON a.au_id = ta.au_id INNER JOIN titles t
    ON t.title_id = ta.title_id INNER JOIN publishers p
    ON t.pub_id = p.pub_id
    WHERE au_fname = @firstname
    AND au_lname = @lastname
    GO

    And when the Stored Proc Executes, it returns the output from the Query to the calling program.

    And if i were to try the same on Informix... i would be displayed with an error message similar to this "INTO TEMP table required for SELECT statement."

    FYI... The calling routine expects the output as a resultset.

    Urgent any help is appreciated.

    Thank you

    Rama

  2. #2
    Join Date
    Sep 2004
    Posts
    13
    You would need the following:

    CREATE PROCEDURE au_info (lastname varchar(40), firstname varchar(20))
    RETURNING varhcar(40), varchar(20), varchar(30), varchar(50);
    DEFINE lnameout varchar(40);
    DEFINE fnameout varchar(20);
    DEFINE titleout varchar(30);
    DEFINE pubnameout varchar(50);

    FOREACH
    SELECT au_lname, au_fname, title, pub_name
    INTO lnameout, fnameout, titleout, pubnameout
    FROM authors a INNER JOIN titleauthor ta
    ON a.au_id = ta.au_id INNER JOIN titles t
    ON t.title_id = ta.title_id INNER JOIN publishers p
    ON t.pub_id = p.pub_id
    WHERE au_fname = firstname
    AND au_lname = lastname
    RETURN lnameout, fnameout, titleout, pubnameout WITH RESUME;
    END FOREACH
    END PROCEDURE

    You will obviously need to change the field sizes for the titleout and pubnameout variables.

    Hope this helps,
    Jeremy
    Last edited by Chancetribe; 09-29-04 at 18:05.

  3. #3
    Join Date
    Sep 2004
    Posts
    6

    Question Stored Procs Returning output from Select Queries

    Thank you for the Reply, Jeremy.
    But the proc modified for INFORMIX would return data as individual variables, i need the proc to return the result set as is. The calling routine expects the out put as a Row, just like when a "select Stmt" is executed from DBAccess.

Posting Permissions

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