--- In ib-support@y..., "Ann W. Harrison" <aharrison@i...> 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.

For what it's worth, this behaviour conforms to SQL standard, more or
less.
(If the select returns nothing then the values of variables are not
changed, as per SQL92 rules; however, there should be a completion
condition to let you know that there was no data returned. I don't
know if that part's being done, or not.)

From SQL92, various places:
"1) Let Q be the result of <query specification> S.
2) Case:
<...>
b) If Q is empty, then no SQL-data values are assigned to any targets
identified by the <select
target list>, and a completion condition is raised: no data.
c) Otherwise, values in the row of Q are assigned to their
corresponding targets."

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

Yup.
From SQL92, subclause 6.5 <set function specification>:
"<...>
b) If AVG, MAX, MIN, or SUM is specified, then
Case:
i) If TXA [that's the result set of the query] is empty, then the
result is the null value."

Cheers,
db