如何将逗号分隔的值传递给MySql中的存储过程? [英] How to pass comma separated values to stored procedure in MySql?

问题描述

我已经存储了

    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

我想将逗号分隔的值传递给第3个参数，如何传递这些值? 我尝试过-

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

但仅显示记录的分支ID为20，而不是30.

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

先感谢

推荐答案

您可以在过程中传递逗号分隔的值，但是您需要使用准备好的语句来使用它，因为传递的值应在查询中串联在一起.

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 ;

这篇关于如何将逗号分隔的值传递给MySql中的存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！