MySQL: IF in a stored procedure

Question

I just get tired of wet stored procedures. According to the tutorials I saw, this should really be (MySQL 5.5):

CREATE PROCEDURE someFunction ( a VARCHAR(256), b VARCHAR(256) ) BEGIN DECLARE haveAllVariables INT; SET haveAllVariables = 1; IF a = "" THEN SET haveAllVariables = 0 ELSEIF b = "" THEN SET haveAllVariables = 0 END IF; 

However, this causes this error:

 ERROR 1064 (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 'ELSEI F b = "" THEN SET haveAllVariables = 0 

Where is the syntax error?

Thanks.

Answer 1

You are missing a semicolon

 CREATE PROCEDURE someFunction ( a VARCHAR(256), b VARCHAR(256) ) BEGIN DECLARE haveAllVariables INT; SET haveAllVariables = 1; IF a = "" THEN SET haveAllVariables = 0; ELSEIF b = "" THEN SET haveAllVariables = 0; END IF; 

Answer 2

Stored procedures are a bit complicated. But here is an example that I checked and laid out for you. In your example, you were missing a couple of semicolons and the final "END".

 DELIMITER $$ CREATE PROCEDURE someFunction ( a VARCHAR(256), b VARCHAR(256) ) BEGIN DECLARE haveAllVariables INT; SET haveAllVariables = 1; IF a = '' THEN SET haveAllVariables = 0; ELSEIF b = '' THEN SET haveAllVariables = 0; END IF; END $$