mardi 24 janvier 2017

MySQL -- stored procedure -- IF NOT EXISTS won't work and neither DECLARE/SET

I try to insert the record in CodesPostaux only if the postal code is not yet in the table.

This is the stored procedure:

DROP PROCEDURE IF EXISTS `Aj_Localité`;
delimiter //
CREATE DEFINER = 'root'@'%' PROCEDURE Aj_Localité(IN par_localité VARCHAR(60),IN par_codepostal VARCHAR(4))
SQL SECURITY INVOKER
BEGIN
    IF NOT EXISTS(SELECT idCodePostal FROM CodesPostaux WHERE CodePostal = 'par_localité') 
    THEN
        INSERT INTO CodesPostaux (CodePostal) VALUES (par_codepostal);
        INSERT INTO Localités (Nom,Ref_CodePostal) VALUES (par_localité,(SELECT MAX(idCodePostal) FROM CodesPostaux));
    ELSE
        INSERT INTO Localités (Nom,Ref_CodePostal) VALUES (par_localité,(SELECT idCodePostal FROM CodesPostaux WHERE CodePostal = 'par_localité'));
    END IF;
END//
delimiter ;

...I tried it like this:

delimiter //
CREATE DEFINER = 'root'@'%' PROCEDURE Aj_Localité(IN par_localité VARCHAR(60),IN par_codepostal VARCHAR(4))
SQL SECURITY INVOKER
BEGIN
    DECLARE result SMALLINT UNSIGNED;
    SET result=(SELECT idCodePostal FROM CodesPostaux WHERE CodePostal = 'par_localité'); 

    IF result IS NULL
    THEN
        INSERT INTO CodesPostaux (CodePostal) VALUES (par_codepostal);
        INSERT INTO Localités (Nom,Ref_CodePostal) VALUES (par_localité,(SELECT MAX(idCodePostal) FROM CodesPostaux));
    ELSE
        INSERT INTO Localités (Nom,Ref_CodePostal) VALUES (par_localité,result);
    END IF;
END//
delimiter ;

...it didn't worked, result was always NULL...

SELECT par_localité;
SELECT par_codepostal;

Both displays the correct values when calling

CALL Aj_localité('Milmort','4041');

I tried many things that I knew from SQL to try set a value to result different than NULL... but it is ALWAYS NULL...

I tried:

  • using the '@' (don't know the difference but there is no need to declare the variable)...
  • the INTO statement like SELECT idCodePostal INTO @result... or at the end of the query CodePostal='par_codepostal') INTO @result;
  • ...many tingz... NONE where working...

I always have the two INSERTS in the THEN part of the IF statement... NEVER the ELSE part... and result IS ALWAYS NULL even if the result of the query gives results...

Aucun commentaire:

Enregistrer un commentaire