CODEWITHSUNDEEP
mysqlstored-procedures
user3756152
user3756152

Reputation: 63

Insert data inside a table in mysql stored procedure

I have a stored procedure which selects value from table1 and then inserts values to table 3 The select id statement may returns multiple values or single value. I've written this stored procedure but it's going in unlimited loop when I run it.

myprocedurename (IN vXID Int(11), IN vYint(21))

BEGIN

SET @myId =( select GROUP_CONCAT(id) from table1 where
            Xid = vXID);

SET @POS =000;
select @myId , @POS;

WHILE (LOCATE(',', @myId ) > 0)
DO
SET @value = ELT(1, @myId );
SET @STR = SUBSTRING(@myId , 1, LOCATE(',',@myId )-1);
SET @myArrayOfValue = SUBSTRING(@myId , LOCATE(',', @myId ) + 1);

Set @pos = @POS +1;

Set @u_id = (select u_id from table2 where
            c_id = vYint and track_id =  @STR);

Set @duration = (select duration from  table2 where
            c_id = vYint and track_id =  @STR);

INSERT INTO table3 
(`id1`,
`pos`,
`id2`,
`id3`,
`duration`)
VALUES
(vYint ,
@pos,
@STR,
@u_id,
@duration);
END WHILE;

END

All I want to do is when I get multiple values in first select then I want to loop through them and be able to insert for each value.

Upvotes: 2

Views: 81

Answers (1)

Pleun
Pleun

Reputation: 8920

Your locate does not take a starting position so it will Always find the first ','

LOCATE (search str, str, [position])

You have a pos in your code but you do not use it in your locate.

Try

WHILE (LOCATE(',', @myId , pos ) > 0)

Upvotes: 2

Related Questions