Lukas Zeller wrote:

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

I would test for an SQLCODE along these lines:

for select last_name
from employee
where emp_no = :empno
into :last_name
do begin
suspend;
when SQLCODE nnnnn do
exception no_result_set;

end

Unfortunately, no error is raised when the result set is empty. In the DSQL
environment this is not a problem as the programming environment is richer. It
would make sense to me at least to set an error code if a result set was empty
and then allow stored procedures to test for it.

>
> >If you expect the count to be in the range
> >0-10, not so bad, but still, it's much better to count the results
> >in the stored procedure.
> I don't understand what you mean by "count in the stored procedure"
> here...
>

Declare a variable and increment it in the DO section of the FOR SELECT loop.

In lieu of an error code appearing I would suggest that testing for a counter of
zero is currently the only simple way to detect an empty result set.

Paul
--

Paul Reeves
http://www.ibphoenix.com
taking InterBase further