How to pass comma separated values to stored procedure in MySql?

Question

I have stored procedure like

    CREATE DEFINER=`test`@`%` PROCEDURE  `test`.`get_details`(
    in p_istudid int,
    in p_icourseid int,
    in p_branchid varchar(20)
    )
    BEGIN
    select ...
    ...
    and branch.id in(p_branchid);
    END

I want to pass comma separated values to 3rd parameter, how to pass that values? I tried like -

 call get_details(10,11,'20,30');

but is only showing records have branch id 20, not 30.

Thanx in advance.

Answer 1

You can pass comma separated values in procedures however you need to use prepared statement to use it, since the values you pass should be concatenated in the query.

delimiter //
   CREATE DEFINER=`test`@`%` PROCEDURE  `test`.`get_details`(
    in p_istudid int,
    in p_icourseid int,
    in p_branchid varchar(20)
    )
    BEGIN
    set @qry = concat('select .... and branch.id in (\'',p_branchid,'\')');
    prepare stmp from @qry;
    execute stmp ;
    deallocate prepare stmp;
    END;//
delimiter ;