Why won't simple If ELSE Statement work in mySql

Question

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

Solution 1

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.

Solution 2

Remember the IF ELSE statement is always used in stored procedure, triggers not in simple select query. And ELSE OR IF keyword always write in new line not in front of query.Like below

Correct syntax:

DELIMITER //

CREATE PROCEDURE NAME(IN Number INT)
BEGIN
    IF roll= 1 THEN
        SELECT * FROM table1 WHERE id = roll;
    ELSE
        SELECT * FROM table2 WHERE id = 2;
    END IF;
END //

DELIMITER ;

Wrong syntax:

DELIMITER //

CREATE PROCEDURE NAME(IN Number INT)
BEGIN
    IF roll= 1 THEN  SELECT * FROM table1 WHERE id = roll;
    ELSE   SELECT * FROM table2 WHERE id = 2;
    END IF;
END //

DELIMITER ;