spas2k spas2k - 6 months ago 22
MySQL Question

mysql stored proc if else with select not working

Can anyone help me get started with creating this stored procedure? I'm just learning and I can't figure out why this doesn't work. I Know it's something simple but I can't find any examples that show what is wrong.

Error code 1064, SQL state 42000: 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 'END IF' at line 1
Line 7, column 1

CREATE PROCEDURE `su_verify_1`(IN inWks VARCHAR(15))
If inWks = 'WN-IA15HXY41135' THEN
select 'no' as result, '3' as errorcode, 'This is a test' as message;
ELSE
select 'yes' as result;
END IF;


Thanks for the help.

Answer

You need to set a delimiter other than a semicolon, so that MySQL knows where the procedure definition ends. As it stands, MySQL is currently seeing this:

CREATE PROCEDURE `su_verify_1`(IN inWks VARCHAR(15))
   If inWks = 'WN-IA15HXY41135' THEN
select 'no' as result, '3' as errorcode, 'This is a test' as message;

With the rest of the lines treated as separate commands. Use the DELIMITER command to change this:

DELIMITER //
CREATE PROCEDURE `su_verify_1`(IN inWks VARCHAR(15))
BEGIN
    IF inWks = 'WN-IA15HXY41135' THEN
        SELECT 'no' AS result, '3' AS errorcode, 'This is a test' AS message;
    ELSE
        SELECT 'yes' AS result;
    END IF;
END//

DELIMITER ;
Comments