Question 112836 in Mysql Question posted in
The official MySQL documentation can be found here.

Sending comma separated input params into the stored proc and looping each param in MYSQL

I have a simple stored procedure with comma separated input params. I will send Database Names in Json or Comma separated values.
Like DB1,DB2,DB3 etc..;
When ever I send that parameter will be loading that table in each of the Database.
I’m looking for loop condition and splitting values.

EXEC   student_info (DBName = 'DB1,DB2,DB3,DB')

    DELIMITER &&  
    ALTER PROCEDURE student_info (IN DBName varchar(100))  
    BEGIN  
     **Splitting comma separated**
     **loop condition**  
        INSERT INTO @DBName.tbl_student(Name,Class)
        SELECT Name,Class FROM DB.student_info  ;  

    END &&  
    DELIMITER ;

What is the best way?

Tags:

2

Answers


  1. 0

    (Answer to the original question, before the edit)

    Use FIND_IN_SET instead of a loop

    SELECT Name,Class 
FROM student_info 
where FIND_IN_SET(ID, studentID);
    Login or Signup to reply.
  2. 0

    You need to use prepared statements (see: PREPARE)

    drop procedure if exists student_info;
DELIMITER &&  
CREATE PROCEDURE student_info(IN dbname varchar(100))  
BEGIN  
    declare a CHAR(16);
    declare s VARCHAR(400);
    DECLARE cur1 CURSOR FOR
    with recursive cte as (
      select 
         @dbname as s1,
         substring_index(substring_index(@dbname,',',1),',',-1) as s2, 
         1 as x
      union all
      select 
         s1, 
         substring_index(substring_index(s1,',',x+1),',',-1), 
         x+1
      from cte 
      where x< (select length(s1)-length(replace(s1,',',''))+1)
      )
      select s2 from cte;

    open cur1;
     
    read_loop: loop
        fetch cur1 into a;
        set @s = CONCAT('INSERT INTO ',a,'.tbl_student(Name,Class) SELECT Name,Class FROM DB.student_info  ');
        PREPARE stmt1 FROM  @s;
        execute stmt1 ;
        deallocate prepare stmt1;
    END loop;

    close cur1;
END &&  
DELIMITER ;

    I tested with this:

    drop table if exists DB1.tbl_student;
drop table if exists DB2.tbl_student;
drop table if exists DB3.tbl_student;
drop table if exists DB.tbl_student;
drop table if exists DB.student_info;
create table DB.tbl_student(name varchar(100),class varchar(100));
create table DB1.tbl_student(name varchar(100),class varchar(100));
create table DB2.tbl_student(name varchar(100),class varchar(100));
create table DB3.tbl_student(name varchar(100),class varchar(100));

create table DB.student_info(name varchar(100),class varchar(100));
insert into DB.student_info values ('Willem', 'Mathematics');
insert into DB.student_info values ('John', 'Mathematics');
insert into DB.student_info values ('Trudy', 'Mathematics');

CALL   student_info ('DB1,DB2,DB3,DB');

    After this the data checked to be copied from student_info to the different tbl_student tables.

    NOTE:
    I would not create those tables, and copy the data, but I would use a view:

    create view db1.tbl_student as select name,class from db.student_info;

    When you create a view, there is no need to copy the data to the other database, because this view will be updated automatically when records in the table db.student_info are updated/deleted or inserted.

    P.S. Above was testen on MySQL 8.x. Some functionality that is not available in earlier versions of MySQL (like WITH) is used.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search