mysql - calling stored procedure inside another stored procedure

Question

Since mysql functions cannot return tables/result sets therefore i created a stored procedure (Get_StudentsWithAllIndicators) that does the stuff i needed. Now, i need to use this stored procedure result inside the actual stored procedure(Find_MapDetails) like this.

select * from students where studentid in (Get_StudentsWithAllIndicators('7,8', 2));

but it does not work! If you are recommending creating a temporary table and insert values into it please tell me the syntax..thanks

Get_StudentsWithAllIndicators

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  <= 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

Get_StudentsWitHAllIndicators -- Is this SP used somewhere else? I have this feeling that you don't really have to create a SP just for this, anyway, I have submitted an answer below. Could you please check? — beejm
– beejm, Commented Mar 28, 2017 at 4:38
i think this sp will only confuse everyone but i have added it any way :) — Samra
– Samra, Commented Mar 28, 2017 at 4:40

beejm · Accepted Answer · 2017-03-28 04:35:51Z

1

INSERT INTO #MYTEMPTABLE exec Get_StudentsWithAllIndicators('7,8', 2)   

SELECT * FROM students where studentId in (SELECT studentid FROM #MYTEMPTABLE)

Save the results of the first sp into a temp table.

answered Mar 28, 2017 at 4:35

beejm

2,4811 gold badge13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Samra Over a year ago

do i have to create the temp table before calling insert? it does not work CREATE TEMPORARY TABLE temp1( StudentID int); INSERT INTO temp1 exec Get_StudentsWithAllIndicators('7,8', 2) ;

beejm Over a year ago

yes you have to create the temp table first. it should have the same column (and column type) with the result set of your first SP

Samra Over a year ago

CREATE TABLE temp1( StudentID int); INSERT INTO temp1 exec Get_StudentsWithAllIndicators('7,8', 2) ;

beejm Over a year ago

Could you try 'CALL' instead of 'EXEC' or remove the 'EXEC' altogether; not very familiar with calling SPs with mySQL

Samra Over a year ago

after some trial and error select is working INSERT INTO temp1 select Get_StudentsWithAllIndicators(indicatorIds, noOfIndicators) ;

|

Collectives™ on Stack Overflow

mysql - calling stored procedure inside another stored procedure

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related