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

Colin,

Use a *left* join to force the table to be the first scanned.

select
T.Column1,
T.Column2,
T.Column3,
T2.Column2,
S.SpecialColumn1,
S.SpecialColumn2,
S.SpecialColumn3,
S.SpecialColumn4
from
Table1 T
inner join Table2 T2 ON (T2.Column1 = T.Column5)
LEFT join SoredProc(T.Column1) S
where
(T.Column4 <> 5)


see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br