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