MySQL Stored Procedure with If...else not executing

Question

CREATE DEFINER=`root`@`localhost` PROCEDURE `Mobile_ForgotPassword`(IN`v_email` VARCHAR(30), IN `v_valid` INT)
NO SQL
IF (v_valid = 0) THEN
    SELECT email FROM user_registration WHERE email = v_email;
ELSE
    SELECT password FROM user_registration WHERE email = v_email;
END IF;

I have this MySQL Stored procedure and its not working. Whenever i try to execute this script on phpmyadmin, getting error on line 4 that i have syntax error. As i am using phpmyadmin, delimiter already added dynamically and i have a privileges to root account. I tried removing DEFINER but still it isn't working. As per my knowledge, i am not seeing any syntax error in script or i am not getting it. Anyone ?

Mahadev · Accepted Answer · 2015-03-19 06:28:55Z

1

CREATE DEFINER=`root`@`localhost` PROCEDURE `Mobile_ForgotPassword`(IN `v_email` VARCHAR(30), IN `v_valid` INT) 
NO SQL 
IF (v_valid = 0) THEN 
    SELECT email FROM user_registration WHERE email = v_email LIMIT 1;
ELSE 
SELECT password FROM user_registration WHERE email = v_email LIMIT 1; 
END IF;

Removed default Delimiter from textbox under script editor in phpMyAdmin

answered Mar 19, 2015 at 6:28

Mahadev

8561 gold badge19 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Navjot Singh · Accepted Answer · 2015-03-19 05:38:19Z

0

You need to add DELIMITER

DELIMITER |
    CREATE PROCEDURE `Mobile_ForgotPassword`(IN`v_email` VARCHAR(30), IN `v_valid` INT)
    NO SQL
    IF (v_valid = 0) THEN
        SELECT email FROM user_registration WHERE email = v_email;
    ELSE
        SELECT PASSWORD FROM user_registration WHERE email = v_email;
    END IF;
    |
    DELIMITER ;

edited Mar 19, 2015 at 5:38

answered Mar 19, 2015 at 5:31

Navjot Singh

5144 silver badges14 bronze badges

4 Comments

Mahadev Over a year ago

Nope. Tried that and failed. And as i already mentioned, i am using phpmyadmin to run this Procedure and phpmyadmin adds delimiter itself

Navjot Singh Over a year ago

it's working fine in my phpmyadmin as well as sqlyog. you just copy and paste in you sql editor and run query.

Mahadev Over a year ago

Ok, let me try again.

Mahadev Over a year ago

I tried to run the script you gave me. It did create the procedure with this error : '#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1'. phpmyadmin shows procedure in list but with not query in it.

Collectives™ on Stack Overflow

MySQL Stored Procedure with If...else not executing

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related