Problem with dynamic delete procedure in mysql

Question

I’ve been doing some research on the Internet but I haven’t had much success. I am mounting a precedent to delete values dynamically relative to the dependencies of a given table but I can pass only a fixed amount of values. Example: If I need to delete 10 records, I have to insert 10 variables in the header of my database. I would like to find a dynamic way to solve the problem. Follow the base process created

DELIMITER //
CREATE PROCEDURE EXCLUIR_USUARIO (IN PARAMETRO INT)
BEGIN

delete from socio_pessoa_juridica where emp_codigo in (PARAMETRO);
delete from empresa where emp_codigo in PARAMETRO;
END;
//
DELIMITER ;

I would like to find a way where I can pass on the call the amount I want of delete keys without having to create too many variables.

Answer 1

I don’t know a native way to do that, something similar to Rest Parameter javascript, usually in this type of situation, a type parameter is created varchar that will receive all data already separated by comma:

delimiter //

create procedure excluir_usuario (in codigo_empresas varchar(255))
begin

  set @sql = concat('delete from socio_pessoa_juridica where emp_codigo in (', codigo_empresas, ')');
  prepare statement from @sql;
  execute statement;
  deallocate prepare statement;

  set @sql = concat('delete from empresa where emp_codigo in (', codigo_empresas, ')');
  prepare statement from @sql;
  execute statement;
  deallocate prepare statement;

end
//

delimiter ;

With this, we can pass different quantities of parameters to the in in the where, making it a little more dynamic, but still limited:

set @codigo_das_empresas = '1,2,3,4,5' ;

call excluir_usuario(@codigo_das_empresas) ;

See online: https://www.db-fiddle.com/f/cpmMCYjwNYeaTDgQ3pRE6g/2

---

Another way, which I believe is more about receiving several different parameters, is to receive a json as a parameter:

delimiter //

create procedure excluir_usuario (in params json)
begin

  set @empresas = json_unquote(json_extract(params, '$.empresas'));

  set @sql = concat('delete from socio_pessoa_juridica where emp_codigo in (', @empresas, ')');
  prepare statement from @sql;
  execute statement;
  deallocate prepare statement;

  set @sql = concat('delete from empresa where emp_codigo in (', @empresas, ')');
  prepare statement from @sql;
  execute statement;
  deallocate prepare statement;

end
//

delimiter ;

Here I could receive different parameters, in different ways, I believe that the example of varchar already meets you, but here is the example, maybe give you other ideas:

set @codigo_das_empresas = json_object( 'empresas', '1,2,3,4,5' );

call excluir_usuario(@codigo_das_empresas) ;

See online: https://www.db-fiddle.com/f/9gqgYjvMSbSDpTymp4vF48/0