Matin Lotfaliee Matin Lotfaliee - 2 months ago 6
MySQL Question

How to select some columns of a store 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?

Answer

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