create multi valued variable inside mysql stored procedure

Question

I have two IN variables into my MYSQL stored procedure. I pass one into a select statement and the value is taken in a temporary variable which is passed to the second SQL statement.

CREATE PROCEDURE `usp_mystoredproc`(IN sText varchar(25), IN myId int(21))
begin
set @myCid= ( SELECT tabId AS id 
FROM table1
WHERE IFNULL(tabId , '') != '' and table1_id= myId);
SELECT searchItem 
FROM table2 
WHERE searchItem LIKE CONCAT(sText , '%') and table2_id = @myCid
LIMIT 15 ;
END

Now the issue is, in the above stored procedure, @myCid gets multiple values and it fails. I need to get all those ids and pass them to the second SQL. How do I handle this?

Answer 1

Join table1 and table2. Something like:

SELECT t2.searchItem 
FROM table2 as t2
JOIN table1 as t1
 ON t1.tabid = t2.table2_id
WHERE t2.searchItem LIKE CONCAT(sText , '%') 
 AND IFNULL(tabId , '') != '' and t1.table1_id= myId
LIMIT 15 ;

Bottom line, you don't need the @myCid variable

• user3756152: Indeed!! I saw this and thought what was I thinking.. Many thanks Lennart
• Mansi Chaudhari: @lennart : if I don't want to use joins then how do I handle this
• Lennart - Slava Ukraini: @MansiChaudari, ask a new question where you describe the reason that you dont want to use a join. Make a link to this question if it helps to understand your scemario