At 14:39 -0400 18.6.2001, you, Ann W. Harrison wrote:

>At 11:43 PM 6/17/2001 +0200, Christian Gütter wrote:
>
>>Interbase behaves differently in two situations which are almost the
>>same.
>>
>>1) When doing a SELECT outside a stored proc, it returns NULLs.
>>
> >2) When doing the same SELECT inside a stored proc, it leaves the result
>> variables untouched, they are not NULL.
>>
> >For me, this is not consistent behaviour.
>>So one might call it a bug, not a serious one, but still a bug.
>
>Sorry, no, it's not a bug. As Helen said, a select that returns
>nothing doesn't change the variables used in the INTO clause. If
>they were null to start, they'll still be null. If not, not.

Agreed. But I still think it should be documented clearly.

>Nor is the behavior of SUM a bug. The sum of zero rows is null.
>Intuitive or not, that's the behavior required by the SQL standard.

So I knew whom (or at least what) to blame :-) :-)

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

Eventually this?

FOR SELECT ... INTO :var DO
<use var>
END

Would it work for sums to?

sumvar=0
FOR SELECT SUM(col) ... INTO :var DO
sumvar=var
END

I mean, would this avoid that sumvar is ever set to NULL when
the SELECT does not return a number?

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

--
Lukas Zeller (luz@...)
-
Synthesis AG, Sustainable Software Concepts
info@..., http://www.synthesis.ch