Hi,

> How to put inside a selectable Stored Procedure another Stored Procedure?
> I have following procedure. problem here is that only one row is
> returned from there (there is suppose to be lot more). What may be wrong
> here?
>
> CREATE PROCEDURE sp_toopaev_tunnid
> RETURNS(
> fkood VARCHAR(10),
> isikid VARCHAR(11),
> too_tegevus VARCHAR(2),
> algus date,
> lopp date,
> tootatud_oosel double precision,
> tootatud_paeval double precision,
> tootatud_ohtul double precision)
> AS
> BEGIN
> for select fkood, isikid, too_tegevus, algus, lopp from v_toopaev into
> :fkood, :isikid, :too_tegevus, :algus, :lopp
> DO

DO starts a loop.

> select tootatud_oosel, tootatud_paeval, tootatud_ohtul from
> SP_TOOPAEV_TUNDE(:algus, :lopp) into :tootatud_oosel, :tootatud_paeval,
> :tootatud_ohtul;

The SELECT gets looped for each row in the outer FOR SELECT.

But, the ";" signals the end of the DO block -> so ...

> SUSPEND;

Only the last values get SUSPENDed and returned to the client.

Try:

FOR SELECT ...
DO BEGIN
select tootatud_oosel, tootatud_paeval, tootatud_ohtul from
SP_TOOPAEV_TUNDE(:algus, :lopp) into :tootatud_oosel, :tootatud_paeval,
:tootatud_ohtul;
SUSPEND;
END

This suspend each time the outer loop calls the inner proc.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com