I have a stored procedure like this:
CREATE PROCEDURE `RankAll`()
READS SQL DATA
SQL SECURITY INVOKER
select userID,sum(score) as score,@rank := @rank + 1 AS rank from
select userID,score from ScoreMessages
select userID,score from ScoreExams
) as scores
JOIN (SELECT @rank := 0) rank
group by userID
order by score desc;
select * from (Call `RankAll`())
It's not possible to use the resultset(s) returned from a procedure within another SQL statement. A procedure cannot be referenced as row source within a query.
If we were using SQL Server, you could write a table valued function. But we can't do that in MySQL. The closest we get in MySQL is having a procedure populate a temporary table, and then reference the temporary table in a separate query.
With the example shown here, the query could be taken out of the procedure, and be used as an inline view definition. That is, replace the
Call RankAll() with the actual query. And assign a table alias of course.
SELECT v.userid , v.score , v.rank FROM ( -- inline view SELECT s.userid , SUM(s.score) AS score , @rank := @rank + 1 AS rank FROM ( SELECT m.userid , m.score FROM ScoreMessages m UNION ALL SELECT e.userid , e.score FROM ScoreExams e ) s CROSS JOIN (SELECT @rank := 0) i GROUP BY s.userid ORDER BY s.score DESC ) v