Mysql Create Insert Procedure Statement incomplete

Question

I'm trying to wirte a little log procedure for my database. I create a procedure with this statment:

 create procedure prc_wirte_log (
    in p_schema varchar(255),
    in p_item varchar(255),
    in p_message varchar(255)
)
begin
    insert into weather.log (`schema`, item, message) values (p_schema, p_item, p_message);
end;

I get the error Error Code: 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 7 0.063 sec

Why? The MySQL Workbench means Incomplet Statment: excepting ; after the insert query.

What could I do?

Answer 1

Multistatement procedures (assumed when BEGIN...END is present) require delimiter overrides to prevent the statements they contain from terminating the procedure definition prematurely.

Typically, you need to do something like:

DELIMITER //

CREATE PROCEDURE blah()
BEGIN
   statements;
END//

DELIMITER ;

The first example on the documentation here demonstrates this (though the last two on that page seem to repeat your mistake.

Answer 2

If you are using WorkBench or similar tool just right click on StoredProcedures and click Create stored procedure the tool will create default structure like below and you could write your logic and hit on apply. Ensure to use semicolon at the end of the last statement (just before END).

CREATE PROCEDURE `new_procedure` ()
BEGIN
select * from tasks;
END