Procedure is not working

Question

I have written below store procedure to delete users from different tables. But somehow it is not working. If I keep individual query inside the procedure it is working, but if I add more then one it is not working.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteUser`(IN `userid` INT)
    NO SQL
BEGIN
DELETE FROM `Table1` WHERE id=userid;
DELETE FROM `Table2` WHERE user_id=userid;
DELETE FROM `Table3` WHERE userid=userid;
END$$
DELIMITER ;

Any error while running it ? – Mayank Pandeyz Apr 12 '17 at 08:53 — Mayank Pandeyz, Apr 12 '17 at 08:53
when I ran it using phpmyadmin it does not show any errors? – Suresh Kamrushi Apr 12 '17 at 08:55 — Suresh Kamrushi, Apr 12 '17 at 08:55

wchiquito · Answer 1 · 2017-04-12T13:01:50.877

I can't reproduce the problem:

DROP PROCEDURE IF EXISTS `deleteUser`;
DROP TABLE IF EXISTS `Table3`, `Table2`, `Table1`;

CREATE TABLE IF NOT EXISTS `Table1` (
    `id` INT
);

CREATE TABLE IF NOT EXISTS `Table2` (
    `user_id` INT
);

CREATE TABLE IF NOT EXISTS `Table3` (
    `userid` INT
);

INSERT INTO `Table1` VALUES (100), (200), (300), (400);
INSERT INTO `Table2` VALUES (100), (600), (700), (800);
INSERT INTO `Table3` VALUES (100), (900), (1000), (1100);

SELECT `id` FROM `Table1`;
SELECT `user_id` FROM `Table2`;
SELECT `userid` FROM `Table3`;

DELIMITER \\

CREATE PROCEDURE `deleteUser`(IN `_userid` INT)
BEGIN
    DELETE FROM `Table1` WHERE `id` = `_userid`;
    DELETE FROM `Table2` WHERE `user_id` = `_userid`;
    DELETE FROM `Table3` WHERE `userid` = `_userid`;
END\\

DELIMITER ;

CALL `deleteUser`(100);

SELECT `id` FROM `Table1`;
SELECT `user_id` FROM `Table2`;
SELECT `userid` FROM `Table3`;

Example in Rextester.

Procedure is not working

1 Answers1