MySQL - Call several procedures in one query-CodePudding

I have a problem with calling procedure several time in one query. When I call each one separately everything is ok. I do not use MySQL in my everyday work so maybe it is some simple problem :) Thanks for help!

CALL DATA_PROCEDURE('text1', 'text2', 1);
CALL DATA_PROCEDURE('text3', 'text4', 2);
CALL DATA_PROCEDURE('text5', 'text6', 3);
CALL DATA_PROCEDURE('text7', 'text8', 4);

CodePudding user response：

You can try our this code

DELIMITER $$
CREATE PROCEDURE sp_abc()
BEGIN
  CALL sp_a();
  CALL sp_b();
  CALL sp_c();
END$$
DELIMITER

CodePudding user response：

I'm not sure if calling the same procedure serveral times all in a parent procedure would make much sense in this case, because each procedure uses different IN parameters, so you can't simply repeat the same procedure for a predefined X times. However, if all those IN parameters have a sequential pattern like the following:
(textN,textN 1,X)
(textN 2,textN 3,X 1)
(textN 4,textN 5,X 2)
we may consider using more dynamic approach to accomplish the tedious task:

delimiter //
drop procedure if exists repeat_sproc//
create procedure repeat_sproc(counter int unsigned)
begin
declare n int default 1;
declare x int default 1;
lp:loop
if x>counter then
leave lp;
end if;
set @stmt=concat("call DATA_PROCEDURE('text",n,"', 'text",n 1,"',",x,");");
prepare stmt from @stmt;
execute stmt;
deallocate prepare stmt;
set n=n 2;
set x=x 1;
end loop lp; 

end//
delimiter ;
call repeat_sproc(4); -- run the nested procedure four times