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

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?

CodePudding user response：

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.

CodePudding user response：

(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);

CodePudding user response：

create the function as follow query for separate your value:

CREATE FUNCTION [dbo].[SplitComaToTable](@input AS Varchar(4000) )
RETURNS
 @Result TABLE(Value BIGINT)
AS
BEGIN
      DECLARE @str VARCHAR(500)
      DECLARE @ind Int
      IF(@input is not null)
      BEGIN
            SET @ind = CharIndex(',',@input)
            WHILE @ind > 0
            BEGIN
                  SET @str = SUBSTRING(@input,1,@ind-1)
                  SET @input = SUBSTRING(@input,@ind 1,LEN(@input)-@ind)
                  INSERT INTO @Result values (@str)
                  SET @ind = CharIndex(',',@input)
            END
            SET @str = @input
            INSERT INTO @Result values (@str)
      END
      RETURN
END

then use this query to select your value as table and set condition on query:

SELECT Value FROM dbo.SplitComaToTable(@yourIds)
 where Value =yourCondition