If I understand you correctly ..



Suppose you need code an name from sp2 with id as parameter

Try:

begin

for Select s.code, s.name from sp2(:id)

Into :code, :name

Do suspend;



end

Regards,

Paul



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of mcnamara_gio
Sent: Thursday, October 02, 2008 10:08
To: firebird-support@yahoogroups.com
Subject: [firebird-support] call stored procedure from another one.



Hello,

I have a stored procedure that accepts a single parameter and returns
multiple rows with multiple columns. Can I call this stored procedure
from another stored procedure and how do I read its result?

The first procedure is like this:

CREATE PROCEDURE sp1( pid Varchar(15) )
RETURNS (
CODE Varchar(15),
NAME Varchar(50)
//Some more variables.)
AS
BEGIN
for
with recursive test as (
select id, pid, nm, 0 depth
from smth
where pid= :pidd
union all

select smth.id, smth.pid, smth.nm,test.depth+1
from smth
join test on smth.pid=test.id
)

select id, nmfrom test
union all
select id, nm from smth
where ccode=:parent
into :code,:name do
begin
-- Here I want to call another sp read it's return values (all rows
and columns) and put them in return variable of this stored procedure.
end
END^

Is it possible? If yes how?

Thanks in advance





[Non-text portions of this message have been removed]