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