> Hello 'stwizard',

>
> Thursday, May 12, 2016, 12:46:25 AM, you wrote:
>
>> 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 f orm the ADDRESS table.
>
>
>> What might I be doing wrong?
>
> First of all, this is an executable SP, not intended to return a
> result set, so get rid of the RETURNS parameters and declare variables
> instead. While executable SPs *can* return a single-row result set,
> with your for-loop, the only result you would get back would be the
> values from the final iteration of the loop. But that's not the
> reason for your unexpected results.
>
> In your INTO clause, you are missing the colon (:) markers
> that are needed when variables or parameters are referred to in a DSQL
> statement. I seem to recall that they are optional in v.2.5 (not
> sure) but that would not be a reason for me to omit them.
>
> Your problem is your variables. With both local variables and
> parameters that you are using like variables, you need to
>
> (1) Initialise your variables before starting the loop (they start out
> as NULL)
> and
> (2) Re-initialise them at the end of the loop (otherwise, until the
> next time the loop gets a "hit", the variables retain the values that
> existed after the last "hit").
>
> Also, once you fix those problems, I see no point in adding extra cost
> by revisiting the PER_ADDRESS table to verify the non-existence of
> the records you just deleted. If the delete had failed, you would
> already be in an exception condition and would have jumped past that
> block to the last END statement.
>
> You don't actually need ADDRESS1 in your working set, either, since
> you don't do anything with it.
>
> With the correct initialisation/re-initialisation, you already have
> the value of ADDR_ID at that point: either a genuine ID (if the
> current ADDRESS format matches the condition) or the dummy value from the
> initialisation (if no invalid addresses were found for that ADDR_ID in
> PER_ADDRESS).
>
> CREATE PROCEDURE P_CLEAN_ADDR
> AS
> DECLARE ADDR_ID Integer = -1;
> /* DECLARE ADDRESS VarChar(50) = ''; not needed */
> DECLARE PER_ADDR_CNT SmallInt = -1;
>
> begin
> FOR SELECT A.ADDR_ID,
> /* A.ADDRESS1, not needed */
> (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
> begin
> DELETE FROM PER_ADDRESS WHERE ADDR_ID = :ADDR_ID;
> DELETE FROM ADDRESS WHERE ADDR_ID = :ADDR_ID;
> end
> -- re-initialise variables
> ADDR_ID = -1;
> /* ADDRESS = ''; */
> PER_ADDR_CNT = -1;
> END
> end ^^

Very exhaustive and a lot of useful advices ... ;-)

He also could simply have a cascading delete foreign key constraint on PER_ADDRESS referencing ADDRESS.

--
With regards,
Thomas Steinmaurer
http://www.upscene.com

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.