MySQL: IF in stored procedure

Question

I am just getting my feet wet with stored procedures. According to the tutorials that I have seen, this should be valid (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, it is throwing 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 error in my syntax?

Thanks.

score 29 · Accepted Answer · answered Mar 10 '12 at 20:14

29

You're 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;

answered Mar 10 '12 at 20:14

Martin.

10,494
3
42
68

Thank you! It should have been obvious! – dotancohen Mar 10 '12 at 20:20

score 11 · Answer 2 · answered Mar 10 '12 at 20:43

11

Stored procedures are a bit tricky. But here is an example I tested and posted 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 $$

answered Mar 10 '12 at 20:43

Ronald Weidner

690
5
15

Thank you. Actually the `END` is quite a way further down, in code that the parser didn't even get to yet! But it was in fact the missing semicolon that was my problem. – dotancohen Mar 10 '12 at 21:12

MySQL: IF in stored procedure

2 Answers2

Linked