current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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

share|improve this answer
    
Indeed!! I saw this and thought what was I thinking.. Many thanks Lennart –  user3756152 Dec 10 '14 at 16:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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