MySQL怎么将集合传入存储过程,如何将逗号分隔的值传递给MySql中的存储过程？...

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.

解决方案

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 ;