Greetings All,

Firebird 2.5.4

Here is my simple Stored Procedure.  It simply looks for any address in the ADDRESS table that starts with ‘0 ‘  as in “0 SE ADAMS ST” and COUNT(*) how many time it might have been used in PER_ADDRESS and if COUNT() > 0 delete the links from the PER_ADDRESS table.

The next line verifies that there is no remaining links in the PER_ADDRESS table and then deletes the record from the ADDRESS table.

My problem is that even though ADDR_ID 347006 does not exist in the PER_ADDRESS table, the “IF (NOT(EXISTS(SELECT 1..” line thinks there is and skips the deletion of the record form the ADDRESS table. 

What might I be doing wrong?

SET TERM ^^ ;

CREATE PROCEDURE P_CLEAN_ADDR returns (

  ADDR_ID Integer,

  ADDRESS VarChar(50),

  PER_ADDR_CNT SmallInt)

AS

begin

  FOR SELECT A.ADDR_ID,

             A.ADDRESS1,

            (SELECT COUNT(*) FROM PER_ADDRESS PA WHERE PA.ADDR_ID = A.ADDR_ID) AS PER_ADDR_CNT               

        FROM ADDRESS A

       WHERE ADDRESS1 STARTING WITH '0 '

        INTO ADDR_ID, ADDRESS, PER_ADDR_CNT DO

    BEGIN                                 

      IF (PER_ADDR_CNT > 0) THEN

        DELETE FROM PER_ADDRESS PA WHERE PA.ADDR_ID = :ADDR_ID; 

      IF (NOT(EXISTS(SELECT 1

                   FROM PER_ADDRESS

                  WHERE ADDR_ID = :ADDR_ID))) THEN

              DELETE FROM ADDRESS WHERE ADDR_ID = :ADDR_ID;

    END        

end ^^

SET TERM ; ^^