triple-x-gamer triple-x-gamer - 6 months ago 20
MySQL Question

MySQL/MariaDB: Can't create a procedure using the case statement

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

Answer

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