Search code examples
mysqlcasemariadbprocedure

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

Solution

  • 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