Re: Stored Procs
On Fri, 25 Sep 1998, Patel, Parag wrote:
> Hello:
>
> I am in the middle of evaluating INFORMIX and would like to know if it is
> possible to
> write a stored proc to return several sets of data
>
> For example:
>
> create procedure ReturnMultipleResultSets ()>
> select
> t1.column1,
> t1.column2,
> t1.column3
> from
> table_one t1,
> table_two t2
> where
> t1.column1 = t2.column1
>
> select
> t3.column1,
> t3.column2,
> t3.column3
> from
> table_three t3
>
> end procedure
>
> There a various reasons for wanting to do this which I can explain if
> necassary.
> SQL Server and Sybase, for example, provide the above.
With considerable syntactic fixing up, yes:
-- not checked for syntax errors, but it gives the gist of what is needed
CREATE PROCEDURE returnmultipleres() RETURNING INT, INT, INT; DEFINE c1, c2, c3 INTEGER;
FOREACH SELECT t1.column1, t1.column2, t1.column3
INTO c1, c2, c3
FROM table_one t1, table_two t2
WHERE t1.column1 = t2.column1
RETURN c1, c2, c3 WITH RESUME;
END FOREACH;
FOREACH SELECT t3.column1, t3.column2, t3.column3
INTO c1, c2, c3
FROM table_three t3
RETURN c1, c2, c3 WITH RESUME;
END FOREACH;
END PROCEDURE;
The obvious restriction is that the return lists must be the
same for both queries...
Yours,
Jonathan Leffler (jleffler@informix.com) #include <witticism.h>
Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN
Informix IDN for D4GL & Linux -- http://www.informix.com/idn