How to create a Stored Procedure for two tables

Question

I’m trying to do this Stored Procedure, but it’s giving error.

I can do an Insert with proceed?

DELIMITER $$

DROP PROCEDURE IF EXISTS `centralrit`.`sp_InterdDetalhe_Encrypt` $$
CREATE PROCEDURE `centralrit`.`sp_InterdDetalhe_Encrypt`
  (
  in p_id int(11),
  in p_usuario_id int(11),
  in p_nome text,
  in p_cpf text,
  in p_nome_pai text,
  in p_nome_mae text,
  in p_data_cadastro datetime,
  in p_serventia_id int,
  in p_natureza text,
  in p_livro text,
  in p_folha text,
  in p_termo text,
  in p_ativo TINYINT(1),
  in p_lixeira TINYINT(1)
 )
BEGIN
  insert into interditado
  (usuario_id, nome, cpf, nome_pai, nome_mae, data_cadastro)
  values
  (
    p_usuario_id,
    aes_encrypt(p_nome, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
    aes_encrypt(p_cpf, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
    aes_encrypt(p_nome_pai, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
    aes_encrypt(p_nome_mae, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
    p_data_cadastro
  );

insert into interditado_detalhe (interditado_id, usuario_id, serventia_id, data_cadastro as cadastro_detalhe,
natureza, livro, folha, termo, ativo, lixeira) values
  (
    p_id,
    p_usuario_id,
    p_serventia_id,
    p_data_cadastro,
    aes_encrypt(p_natureza, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
    aes_encrypt(p_livro, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
    aes_encrypt(p_folha, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
    aes_encrypt(p_termo, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
    p_ativo,
    p_lixeira
  );

END $$

DELIMITER ;

Error: Script line: 4 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 'as cadastro_detalhe, natureza, livro, folha, term, active, recycle bin) values (' at line 33

Answer 1

The second insert You have a syntax error. Remove the as cadastro_detalhe that will work.

insert into interditado_detalhe 
(interditado_id, usuario_id, serventia_id, data_cadastro /*as cadastro_detalhe*/,
natureza, livro, folha, termo, ativo, lixeira) 
values (...)

Answer 2

Yes, you can do any CRUD with a Stored Procedure for two tables or even more or even the use of CRUD (create, read, update and Delite) in a single Stored Procedure.

Your mistake is because you are trying to rename your column in an INSERT, this is only possible in a SELECT, for cases of INSERT, UPDATE OR DELETE (Where*), it is not necessary that you rename your columns.

Summarizing you do not need to rename your columns in these cases.

Change your Procedure to.

 DELIMITER $$

    DROP PROCEDURE IF EXISTS `centralrit`.`sp_InterdDetalhe_Encrypt` $$
    CREATE PROCEDURE `centralrit`.`sp_InterdDetalhe_Encrypt`
      (
          in p_id int(11),
          in p_usuario_id int(11),
          in p_nome text,
          in p_cpf text,
          in p_nome_pai text,
          in p_nome_mae text,
          in p_data_cadastro datetime,
          in p_serventia_id int,
          in p_natureza text,
          in p_livro text,
          in p_folha text,
          in p_termo text,
          in p_ativo TINYINT(1),
          in p_lixeira TINYINT(1)
     )

    BEGIN
        insert into interditado (usuario_id, nome, cpf, nome_pai, nome_mae, data_cadastro)
        values
        (
            p_usuario_id,
            aes_encrypt(p_nome, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
            aes_encrypt(p_cpf, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
            aes_encrypt(p_nome_pai, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
            aes_encrypt(p_nome_mae, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
            p_data_cadastro
        );

        insert into interditado_detalhe (interditado_id, usuario_id, serventia_id, 
        data_cadastro, natureza, livro, folha, termo, ativo, lixeira) values
        (
            p_id,
            p_usuario_id,
            p_serventia_id,
            p_data_cadastro,
            aes_encrypt(p_natureza, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
            aes_encrypt(p_livro, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
            aes_encrypt(p_folha, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
            aes_encrypt(p_termo, 'MIICeQIBADANBgkqhkiG9w0BAQEFAASC'),
            p_ativo,
            p_lixeira
        );

    END $$

DELIMITER ;