0

I am trying to make a reusable mysql procedure that takes data from join tables and store that in a temporary table. I will then create different procedures to select/calculate data from the result. Here is my SQL code so far:

DELIMITER //
CREATE PROCEDURE getModulesForCourseYear(IN cid VARCHAR(10), IN yr INT(1))
CALL getModulesInCourse(cid);
SELECT * FROM modules WHERE year = yr;//
DELIMITER ;

The getModulesInCourse procedure creates a temporary table called modules

After getModulesInCourse is called in the procedure getModulesForCourseYear, I would then like to filter this result. This is where it fails.

I guess this is happening because mysql does not know what table modules is as it does not currently exist.

How would I be able to select from a temporary table in this procedure?

I am doing it in PHPMyAdmin which gives this error:

SQL query:


CREATE PROCEDURE getModulesForCourseYear(IN cid VARCHAR(10), IN yr INT(1))
CALL getModulesInCourse(cid);
SELECT * FROM modules WHERE year = yr;
MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM modules WHERE year = yr' at line 3

NOTE: I am doing the procedure this way as I want to select the same initial data in multiple procedures, then I will filter it down. This way, it prevents duplicate code and if I need to change it later on, I can do so once.

iProgram
  • 6,057
  • 9
  • 39
  • 80
  • Check this out. It may help. https://stackoverflow.com/questions/17944871/how-to-use-table-output-from-stored-mysql-procedure – O. Jones Jan 03 '19 at 15:06
  • @O.Jones That's why I made the procedure `getModulesInCourse` to store its values in a temporary table. I want `getModulesForCourseYear` to perform actions on the output of `getModulesInCourse`. To do this, `getModulesInCourse` stores its output in the temporary table `modules`. The problem is `getModulesForCourseYear ` cannot access this table. – iProgram Jan 03 '19 at 15:53
  • WIthout being sure, I think your temp table goes out of scope when the procedure creating it returns. The item I posted shows a way of passing a result set from an inner procedure to an outer procedure. – O. Jones Jan 03 '19 at 16:00
  • @O.Jones So this means I have to do the same SELECT statement in all functions then and so I can't reuse it as a function? – iProgram Jan 03 '19 at 16:01

0 Answers0