Chris Chris - 2 months ago 10
MySQL Question

Why won't simple If ELSE Statement work in mySql

I'm trying to create a simple stored procedure with an if else statement in SQLYog against a mySql db. I'm not overly familiar with mySql syntax so i'm hoping it's something simple but I just can't see why this isn't working

CREATE PROCEDURE p(IN Number INT)

IF NUMBER = 1 THEN
SELECT * FROM tblProduct WHERE ProductID = Number
ELSE SELECT * FROM tblProduct WHERE ProductId = 2
END IF


I'd appreciate if anyone can help me with this and tell me where i'm going wrong.
Thanks for reading.

I get the following when I try to execute:

Error Code : 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 'ELSE SELECT * FROM tblProduct where intProductId = 2
END IF' at line 5

Answer

Statements in MySQL are delimited by semicolons. To create procedures with them, you do a little trick like so:

DELIMITER //

CREATE PROCEDURE p(IN Number INT)
BEGIN
    IF NUMBER = 1 THEN
        SELECT * FROM tblProduct WHERE ProductID = Number;
    ELSE
        SELECT * FROM tblProduct WHERE ProductId = 2;
    END IF;
END //

DELIMITER ;

Check out the documentation for if/else for more info.

Comments