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.

Martin. · Accepted Answer · 2012-03-10 20:14:58Z

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, 2012 at 20:14

Martin.

10.5k3 gold badges45 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dotancohen Over a year ago

Thank you! It should have been obvious!

Ronald Weidner · Accepted Answer · 2012-03-10 20:43:12Z

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, 2012 at 20:43

Ronald Weidner

6905 silver badges15 bronze badges

1 Comment

dotancohen Over a year ago

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.

Collectives™ on Stack Overflow

MySQL: IF in stored procedure

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related