title: mysql - calling stored procedure inside another stored procedure
body: <p>Since mysql functions cannot return tables/result sets therefore i created a stored procedure (<strong>Get_StudentsWithAllIndicators</strong>) that does the stuff i needed. Now, i need to use this stored procedure result inside the actual stored procedure(Find_MapDetails) like this. </p>
  
  <pre><code>select * from students where studentid in (Get_StudentsWithAllIndicators('7,8', 2));
  </code></pre>
  
  <p>but it does not work! If you are recommending creating a temporary table and insert values into it please tell me the syntax..thanks</p>
  
  <p><strong>Get_StudentsWithAllIndicators</strong></p>
  
  <pre><code>CREATE PROCEDURE `Get_StudentsWithAllIndicators`(IN p_list VARCHAR(255), IN p_length int)
   BEGIN
  /*make query with the length of indicators in the list*/
  DECLARE x  INT;
  SET x=1;
  SET @queryMain = 'SELECT distinct studentid FROM studentindicators WHERE studentid IN ';
  SET @queryWhere = '(SELECT studentid FROM studentindicators WHERE indicatorid = substring_index(\'';
  SET @query = '';
  
  WHILE x  &lt;= p_length DO
  
  SET @query =  CONCAT(@query, @queryWhere, p_list, '\', ",", 1)) AND studentId IN ');    
  SET x=x+1;
  SET @lengthWithCommas = Length(p_list);
  
  SET p_list = substr(p_list, instr(p_list, ',') + 1, @lengthWithCommas - instr(p_list, ','));
  
  END WHILE;
  
  /*remove last AND - note: no occurence of A after AND is expected*/
  SET @query = CONCAT(@queryMain, LEFT(@query, LENGTH(@query) - LOCATE('A', REVERSE(@query))));
  
  
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  
   END
  </code></pre>