0

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?

0

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

  • Indeed!! I saw this and thought what was I thinking.. Many thanks Lennart – user3756152 Dec 10 '14 at 16:38
  • @lennart : if I don't want to use joins then how do I handle this – Mansi Chaudhari Aug 18 '17 at 6:27
  • @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 – Lennart Aug 18 '17 at 7:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.