2

I am struggling with the case statement of MySQL. I want to use it in a procedure but I am getting an error (#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 4) every time I try to create the procedure. I made the procedure as easy as possible to avoid other mistakes which have nothing to do with the case statement. This is what I was trying at the moment:

drop PROCEDURE if EXISTS test;
create PROCEDURE test()
BEGIN
CASE
when true THEN INSERT INTO testtable VALUES (DEFAULT);
end case;
END

I really don't know where the mistake is. The manual says the sytax is this:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list] 
END CASE

I think I did exactly the same. I hope somebody can help me. I am struggling for several hours now. BTW: I am using XAMPP with PHPMyAdmin

triple-x-gamer
  • 23
  • 4

1 Answers1

0

you need to change your delimiter, otherwise it doesn't know when your stored procedure ends as opposed to statements inside the procedure

if you're running this from console:

drop PROCEDURE if EXISTS test;

delimiter $$

create PROCEDURE test()
BEGIN
CASE
when true THEN INSERT INTO testtable VALUES (DEFAULT);
end case;
END$$

delimiter ;

if you're running it inside phpmyadmin, then you'll have to specify the delimiter with their GUI:

phpmyadmin

Jeff Puckett
  • 37,464
  • 17
  • 118
  • 167
  • Thanks for your answer, but this does not solve the problem. Now I am getting this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'create PROCEDURE test() BEGIN CASE when true THEN INSERT INTO testtable VAL' at line 2 – triple-x-gamer May 26 '16 at 17:46
  • @triple-x-gamer you have to drop the procedure before you change the delimiter, or use the custom delimiter when you drop it. I have edited my answer to demonstrate. – Jeff Puckett May 26 '16 at 17:59
  • @triple-x-gamer also because you're using phpmyadmin, you might find [this answer](http://stackoverflow.com/a/16802061/4233593) helpful for setting the delimiter. it seems to work fine for me, but I did notice it throwing some errors on delimiter despite actually creating the procedure anyway. – Jeff Puckett May 26 '16 at 18:10
  • Oh my god, thank you so much! It works! And it also works when I change the delimiter with the sql statement. It is not necessary to change it with the GUI (at least for me it worked without the GUI). – triple-x-gamer May 26 '16 at 18:52