How to select some columns of a stored procedure in Mysql

I have a stored procedure like this:

CREATE PROCEDURE `RankAll`()
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY INVOKER
COMMENT ''
BEGIN
    select userID,sum(score) as score,@rank := @rank + 1 AS rank  from
    (
        select userID,score from ScoreMessages  
        union all   
        select userID,score from ScoreExams
    ) as scores
    JOIN (SELECT @rank := 0) rank
    group by userID
    order by score desc;
END

Actually I wanted to use it as a view but I have variables because I wanted to rank them and it did not let me to use it in a view.

I tried the query below but it is not correct:

select * from (Call `RankAll`())

So how can I select multiple columns out of it?

Solution

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