How to pass comma separated input parameter to call stored procedure from another stored procedure

Question

I referred this link to pass the comma separated string to run a query. It worked fine! But I used the same method to call a stored procedure, where it shows the following error:

Error Code: 1318. Incorrect number of arguments for PROCEDURE UPDATE_DETAILS; expected 1, got 3

Here is the example I have tried using the method mentioned in the above link,

CREATE DEFINER=`root`@`localhost` PROCEDURE `RUN_JOB`()
BEGIN
   declare ids text;

    select group_concat(id) into ids from table_1;

    set @sql = concat('call 
    UPDATE_DETAILS(',ids, ')');

    PREPARE stmt FROM @sql;

    EXECUTE stmt;

    DEALLOCATE PREPARE stmt;

END



CREATE DEFINER=`root`@`localhost` PROCEDURE `UPDATE_DETAILS`(
IN id_value int)
BEGIN

    update table_2 set 
        col_1 = 'some_value' where id = id_value;

    update table_3 set 
        col_2 = 'some_value' where id = id_value;

END

Is this is the right method or is there any other way this can be achieved?

Thanks in advance!

*"Is this is the right method? Or is there any other way this can be achieved?"* Looks like you trying to implement a dynamic run time in MySQL??? ... Using [common_schema](https://github.com/shlomi-noach/common_schema) (created by [@shlomi-noach](https://stackoverflow.com/users/1505860/shlomi-noach)) with [QueryScript](http://shlomi-noach.github.io/common_schema/query_script.html) then might make more sense.. — Raymond Nijland, Sep 11 '19 at 13:35
Thanks @HeroGoes for this suggestion. I'll try using common_scheme and QueryScript — Shamili, Sep 11 '19 at 13:57
comma separated strings are varchar but you have passed UPDATE_DETAILS's input parameter as int. Hence the error. — Ankit Bajpai, Sep 11 '19 at 15:04

score 1 · Accepted Answer · answered Sep 15 '19 at 12:46

Since you are parsing a comma separated value to a int variable it doesn't work.

Try the below sp. Here used cursor to fetch the id one by one and it will call the sp UPDATE_DETAILS.

CREATE DEFINER=`root`@`localhost` PROCEDURE `RUN_JOB`()
BEGIN

    declare v_id int;
    declare done boolean default false;

    declare cur_id cursor for select id from table_1;

    declare continue handler for not found set done=true;

    open cur_id;

    call_loop: loop

        fetch cur_id into v_id;

            if done then

                leave call_loop;

            end if;

        call UPDATE_DETAILS(v_id);

    end loop;

    close cur_id;

    set done=false;

END

Mark as answer so that it will be useful for others. a tick mark below the vote button. — James, Sep 16 '19 at 07:01

How to pass comma separated input parameter to call stored procedure from another stored procedure

1 Answers1