How to select some columns of a stored procedure in Mysql

Question

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?

I think you have to make the stored procedure save its results in a temporary table. Then you can select from that table. See http://stackoverflow.com/questions/2466713/use-result-set-of-mysql-stored-procedure-in-another-stored-procedure for a similar problem. — Barmar, Sep 30 '16 at 18:55
It's not possible to use the resultset(s) returned from a procedure within another SQL statement. The procedure cannot be referenced as row source for a query. If you were using SQL Server, you could write a table valued function. The closest you are going to get in MySQL is to have the procedure populate a temporary table, and then reference the temporary table in a separate query. With the example shown here, the query in the procedure could be taken out of the procedure, and used as an inline view definition. (Replace the `Call RankAll` with the query. And assign a table alias of course.) — spencer7593, Sep 30 '16 at 18:55

score 1 · Accepted Answer · answered Sep 30 '16 at 19:03

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

score -1 · Answer 2 · answered Sep 30 '16 at 18:40

-1

Just use:

call RankAll()

The call returs the result set as if you would run the same SQL query.

answered Sep 30 '16 at 18:40

slaakso

8,331
2
16
27

So how can I select multiple columns out of it? – Matin Lotfaliee Sep 30 '16 at 18:41
MySQL does not have concept of table valued function. What you need to do is to store the results to a temporary table and use that in the calling procedure / application. – slaakso Oct 01 '16 at 08:43
MySQL temporary tables are visible to the process, whereas in SQL Server temporary tables are visible inside the procedure you create them. In MySQL you can create a temporary table, call the procedure to populate it and then use the result set after the call. So: drop temporary table if exists data_tmp; create temporary table data_tmp (id int, score int, rank int); call RankAll(); /* populate the data_tmp */ select * from data_tmp; – slaakso Oct 01 '16 at 08:50

How to select some columns of a stored procedure in Mysql

2 Answers2