At 01:13 PM 1/10/2007, you wrote:

>Hi,
>
>We currently have a stored procedure to do some stuff that can't be
>done easily with SQL. This procedure takes one id parameter and
>returns a number of different fields that you can use by calling the
>procedure in an SQL Statement.
>
>However, a single SQL statement that uses this procedure might use it
>multiple times to use the different fields the procedure returns.
>This seems quite a waste of resources as we are running exactly the
>same procedure multiple times to get the results.
>
>Here is an example SQL statement which we currently use:
>
>select T.Column1,
> T.Column2,
> T.Column3,
> T2.Column2,
> (Select S.SpecialColumn1 from SoredProc(T.Column1) S),
> (Select S.SpecialColumn2 from SoredProc(T.Column1) S),
> (Select S.SpecialColumn3 from SoredProc(T.Column1) S),
> (Select S.SpecialColumn4 from SoredProc(T.Column1) S)
>from Table1 T
> inner join Table2 T2 ON (T2.Column1 = T.Column5)
>where (T.Column4 <> 5)

Unfortunately, that's how it is. The output from a selectable stored
procedure (SSP) behaves in a lot of ways like a table but, as we
know, it is not a table. So, if you "subquery" a SSP, it has to
execute a lot of times. In your example, it will execute four times
for *each* row in Table1.

However, you certainly can write a SP that calls the other SP. You
can nest a for select loop inside another so that your new SP will
return all the fields you want for all the rows.

>Now we want to add 1 or two more calls to the procedure to get more
>information from it we don't want to call the procedure more times if
>we don't need to.
>
>Is there anything we can do so we can call the procedure just once and
>get all the columns we want from it in one go?
>
>We have tried joining onto the stored procedure, e.g.:
>
> inner join StoredProc(T.Column1) S on (S.SpecialColumn1 = T.Column1)
>
>but this does not work (error 'The cursor identified in the update or
>delete statement is not positioned on a row.
>no current record for fetch operation', so perhaps our stored
>procedure is getting a null??)

Try this.

create procedure newproc (NotCol4Value whatever)
returns (
Column1 whatever,
Column2 whatever,
Column3 whatever,
Col2 whatever,
SpecialColumn1 whatever,
SpecialColumn2 whatever,
SpecialColumn3 whatever,
SpecialColumn4 whatever,
etc. (all the output fields you want, including those from the called SP)
)
as
begin
for select
T.Column1,
T.Column2,
T.Column3,
T2.Column2
from Table1 T
join Table2 T2 ON (T2.Column1 = T.Column5)
where (T.Column4 <> :NotCol4Value)
into :Column1, :Column2, :Column3, :Col2
do begin
if (Column1 is not null) then
for select * from SoredProc(:Column1)
into
:SpecialColumn1,
:SpecialColumn2,
:SpecialColumn3,
:SpecialColumn4,
:etc....
do
SUSPEND;
Column1 = NULL;
end
end

But here's another point. If in fact the existing stored procedure
returns only *one* row for each occurrence of the Column1 key (there
is no FOR SELECT loop in it) then the new SP involves even less
work. Don't use the inner FOR..SELECT.. loop above, just remove the
SUSPEND statement from the existing procedure and do like this:

create procedure newproc (NotCol4Value whatever)
returns (
Column1 whatever,
Column2 whatever,
Column3 whatever,
Col2 whatever,
SpecialColumn1 whatever,
SpecialColumn2 whatever,
SpecialColumn3 whatever,
SpecialColumn4 whatever,
etc. (all the output fields you want, including those from the called SP)
)
as
begin
for select
T.Column1,
T.Column2,
T.Column3,
T2.Column2
from Table1 T
join Table2 T2 ON (T2.Column1 = T.Column5)
where (T.Column4 <> :NotCol4Value)
into :Column1, :Column2, :Column3, :Col2
do begin
if (Column1 is not null) then
begin
EXECUTE PROCEDURE SoredProc(:Column1)
RETURNING_VALUES (
:SpecialColumn1,
:SpecialColumn2,
:SpecialColumn3,
:SpecialColumn4,
:etc....)
SUSPEND;
end
end
end

./heLen