Insert into multiple tables

Question

I am wanting to do the following Insert in Mysql

    CREATE PROCEDURE cadastroLocal (var_localNome varchar(80),  var_horainicial TIME, var_horafinal TIME, var_periodoemminutos INTEGER, var_diasdasemana INTEGER)
BEGIN
   INSERT INTO LOCAL (LOCAL.NOME) VALUES (var_localNome);
   SET @localid  = LAST_INSERT_ID();
   INSERT INTO HORARIO (HORARIO.HORAINICIAL, HORARIO.HORAFINAL, HORARIO.PERIODOEMMINUTOS) VALUES (var_horainicial, var_horafinal, var_periodoemminutos);
   SET @horarioid  = LAST_INSERT_ID();
   INSERT INTO CONFIGURACAODELOCAL (CONFIGURACAODELOCAL.LOCALID, CONFIGURACAODELOCAL.DIADASEMANA, HORARIO.HORARIOID) VALUES (@localid, var_diasdasemana, @horarioid);

But the following error appears:

You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near '' at line 3

Nowhere are there any simple quotes. I’ve looked at documentation and other questions that are for two tables.

Someone gives an idea, or even someone knows if this is possible for more than two Tables?

Answer 1

Your problem happens because of the delimiters. You need to change the delimiter (;) for a moment, so that your program can pass the text from the Procedure to Mysql. For more details, see mysql documentation.

Try:

delimiter //

CREATE PROCEDURE cadastroLocal (var_localNome varchar(80),  
                                var_horainicial TIME, 
                                var_horafinal TIME, 
                                var_periodoemminutos INTEGER, 
                                var_diasdasemana INTEGER)
BEGIN

   INSERT INTO 
      LOCAL (LOCAL.NOME) 
   VALUES 
      (var_localNome);

   SET @localid  = LAST_INSERT_ID();
   INSERT INTO HORARIO (HORARIO.HORAINICIAL, HORARIO.HORAFINAL, HORARIO.PERIODOEMMINUTOS) VALUES (var_horainicial, var_horafinal, var_periodoemminutos)$$
   SET @horarioid  = LAST_INSERT_ID();
   INSERT INTO CONFIGURACAODELOCAL (CONFIGURACAODELOCAL.LOCALID, CONFIGURACAODELOCAL.DIADASEMANA, HORARIO.HORARIOID) VALUES (@localid, var_diasdasemana, @horarioid);

END

//

Update

I changed the delimiter according to @Jesse’s suggestion.

Answer 2

First I would like to thank the support.

Different from the suggested by the Mayan companion, replaces in the create $$ delimiter with the one of the mysql documentation example.

Soon I solved my problem like this:

delimiter //

CREATE PROCEDURE cadastroLocal (var_localNome varchar(80),
                                var_horainicial TIME, 
                                var_horafinal TIME,
                                var_periodoemminutos INTEGER, 
                                var_diasdasemana INTEGER)
BEGIN

   INSERT INTO 
      LOCALL (LOCALL.NOME)
   VALUES 
      (var_localNome);

   SET @localid  = LAST_INSERT_ID();
   INSERT INTO HORARIO (HORARIO.HORAINICIAL, HORARIO.HORAFINAL, HORARIO.PERIODOEMMINUTOS) VALUES (var_horainicial, var_horafinal, var_periodoemminutos);
   SET @horarioid  = LAST_INSERT_ID();
   INSERT INTO CONFIGURACAODELOCAL (CONFIGURACAODELOCAL.LOCALID, CONFIGURACAODELOCAL.DIADASEMANA, CONFIGURACAODELOCAL.HORARIOID) VALUES (@localid, var_diasdasemana, @horarioid);
END 

//

thank you!