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?

Multistatement procedures (assumed when `BEGIN...END` is present) require delimiter overrides to prevent the statements they contain from terminating the procedure definition prematurely. — Uueerdo, Sep 19 '18 at 18:39
Nice this works for me. What dose the delimiter do? Thank you — Markus, Sep 19 '18 at 18:41

Uueerdo · Accepted Answer · 2019-02-06T20:33:15.393

34

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.

edited Feb 06 '19 at 20:33

answered Sep 19 '18 at 18:44

Uueerdo

15,723
1
16
21

4

Thanks. Just adding the "DELIMITER //" statement at the top solved my problem. – Tony Mercado Feb 26 '20 at 07:41

score 2 · Answer 2 · answered Jun 28 '21 at 23:14

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

Mysql Create Insert Procedure Statement incomplete

2 Answers2