DELETE FROM in MYSQL Stored Proceedure

Question

I've created a stored procedure using the following code;

DELIMITER //
CREATE PROCEDURE DeleteCandidate(IN `vid` integer, IN `aid` integer)
  BEGIN
    DELETE FROM `app_appointment` WHERE `vid` = vid and `aid` = aid;
    DELETE FROM `app_declare` WHERE `vid` = vid and `aid` = aid;
  END //
DELIMITER ;

When I call the procedure Call DeleteCandidate(1, 1547), rather than deleting where vid = vid and aid = aid, it deletes all the data from the tables.

Can anyone help me on why this would be happening. When I debug in dbForge Studio it shows the values of aid = 1547 and vid = 1.

Many thanks

Answer 1

Use different names for your variables. Otherwise the DB thinks you compare the column content to itself (instead of the column to the variable) and this is always true

DELIMITER //
CREATE PROCEDURE DeleteCandidate(IN `vidparam` integer, IN `aidparam` integer)
  BEGIN
    DELETE FROM `app_appointment` WHERE `vid` = vidparam and `aid` = aidparam;
    DELETE FROM `app_declare` WHERE `vid` = vidparam and `aid` = aidparam;
  END //
DELIMITER ;