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

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

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.