CODEWITHSUNDEEP
mysqlstored-proceduresprepared-statement
Shamili
Shamili

Reputation: 83

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

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!

Upvotes: 1

Views: 737

Answers (1)

James
James

Reputation: 1829

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

Upvotes: 1

Related Questions