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)

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??)

I hope you can get the idea of what I am trying.

Any help appreciated
Regards
Colin