Subject RE: [ib-support] SELECT in stored proc returns wrong value
Author Ann W. Harrison
>I had written:

> >As for doing a "select count (*) ... " before doing a select or
> >update, that's generally a terrible idea because it doubles the
> >amount of work done.

At 10:32 AM 6/19/2001 +0200, Lukas Zeller wrote:

>Agreed. But IS there a better method to see if a SELECT ... INTO
>has returned a result or not than initializing the variable
>with a value that cannot be returned (which can be quite tricky
>up to impossible, depending on the column)?

In a stored procedure, the code between the DO and the
END will be executed only if a result was returned. Does
that help? Thus

counter = 0
FOR SELECT .... INTO ...
DO
counter = counter + 1;
<whatever else>
END

if counter = 0
then <whatever>
else <whatever>

>Would this avoid that sumvar is ever set to NULL when
>the SELECT does not return a number?
>
>sumvar=0
>FOR SELECT SUM(col) ... INTO :var DO
> sumvar=var
>END

No. You'd have to do the arithmetic in the stored
procedure - it's not that much more expensive than
having the engine do it. Remember to discard null
values.


Regards,

Ann
www.ibphoenix.com
We have answers.