--- In IBObjects@y..., "Helen Borrie (TeamIBO)" <helebor@d...> wrote:

> >
> >CREATE PROCEDURE SPS_CUSTOMER$ADDRESS_EXISTS
> > (ACITY_CODE VARCHAR(5),
> > ACITY_STREET_ID INTEGER,
> > ASTREET_NUMBER VARCHAR(10),
> > ABUILDING_REG VARCHAR(10))
> >RETURNS
> > (ARESULT SMALLINT)
> >AS
> >BEGIN
> >...
> >IF (EXISTS(SELECT * FROM CUSTOMER
> >WHERE (CUSTOMER.CITY_CODE = :ACITY_CODE)
> > AND (((:ACITY_STREET_ID IS NULL) AND (CUSTOMER.CITY_STREET_ID IS
> >NULL)) OR (CUSTOMER.CITY_STREET_ID = :ACITY_STREET_ID))
> > AND (CUSTOMER.STREET_NUMBER_UPPER = :ASTREET_NUMBER) AND
> >(CUSTOMER.BUILDING_REG_UPPER = :ABUILDING_REG)))
> >THEN
> > ARESULT = 1;
> >ELSE
> > ARESULT = 0;
> >...
> >END
> >
>
> "works fine" in the sense that it doesn't throw an error? In PSQL

the parser probably allowed it somehow because you had
ACITY_STREET_ID defined as an input. As such, it could be treated as
a variable within your procedure. IMO, its illegal use in the WHERE
clause ought to have prevented the procedure from compiling. If it
DID compile, then I think we are seeing a parser error in the
database.

>
> Regardless of what the PSQL parser allowed, the matter of whether

the procedure's input argument has a null value in that sub-clause
has NO bearing on the result of the EXISTS() predicate and it has no
business being there.

>

Either I really don't understand or above claim is not correct - I
think comparision NULL = NULL is evaluated to FALSE (or UNKNOWN ? -
in Firebird and Interbase manuals this part is not very clear and in
one chapter the result of this comparision is stated as FALSE, in
other chapter as UNKNOWN...but it doesn't matter, because result of
EXISTS() is the same) so matter of whether the procedure's input
argument has a null value in that sub-clause HAS bearing on the
result of the EXISTS().

"Work fine" in sense that stored procedure returns valid result:
without part "(:ACITY_STREET_ID IS NULL) ..." of WHERE clause I have
to split above statement into two parts with IF...THEN to get the
same result:

CREATE PROCEDURE SPS_CUSTOMER$ADDRESS_EXISTS
(ACITY_CODE VARCHAR(5),
ACITY_STREET_ID INTEGER,
ASTREET_NUMBER VARCHAR(10),
ABUILDING_REG VARCHAR(10))
RETURNS
(ARESULT SMALLINT)
AS
BEGIN
...
IF (ACITY_STREET_ID IS NULL) THEN
BEGIN
IF (EXISTS(SELECT * FROM CUSTOMER
WHERE (CUSTOMER.CITY_CODE = :ACITY_CODE)
AND (CUSTOMER.CITY_STREET_ID IS NULL)
AND (CUSTOMER.STREET_NUMBER_UPPER = :ASTREET_NUMBER)
AND (CUSTOMER.BUILDING_REG_UPPER = :ABUILDING_REG)))
THEN
ARESULT = 1;
ELSE
ARESULT = 0;
END
ELSE
BEGIN
IF (EXISTS(SELECT * FROM CUSTOMER
WHERE (CUSTOMER.CITY_CODE = :ACITY_CODE)
AND (CUSTOMER.CITY_STREET_ID = :ACITY_STREET_ID)
AND (CUSTOMER.STREET_NUMBER_UPPER = :ASTREET_NUMBER)
AND (CUSTOMER.BUILDING_REG_UPPER = :ABUILDING_REG)))
THEN
ARESULT = 1;
ELSE
ARESULT = 0;
END

...
END

This construction is pretty complex compared to original...
The same construction I use e.g.
(:ARDB$DB_KEY IS NULL) OR (CUSTOMER.RDB$DB_KEY <> :ARDB$DB_KEY)
in OnBeforePost events common for evaluating various criteria in
posting from dssInsert and dssEdit states (in the case of dssInsert
is important part (:ARDB$DB_KEY IS NULL), in the case of dssEdit the
second (CUSTOMER.RDB$DB_KEY <> :ARDB$DB_KEY)..., but I can use the
same stored procedure for "before post check".

Helen Borrie - you and others from TEAM IBO are my "GURU" in the
field of Interbase and Firebird and IBObjects and I think nobody
believes HOW MUCH I LEARNED ONLY FROM READING OF YOURS POSTINGS IN
THIS GROUP - SO I HAVE TO SAY: THANKS...

Tibor