MySQL Procedure multiple results

Question

The basic ideea is that when I call a procedure I want to get a set of data not just one value (by setting results to be INT and selection COUNT(id) for instance). I would like to know how to change this procedure to get multiple values...

DELIMITER //
 CREATE PROCEDURE getnames(IN id_in INT,OUT results ???)
    BEGIN
        set results=(select name from people where id>id_in);
    END //
 DELIMITER ;

score 2 · Accepted Answer · answered Oct 28 '11 at 09:54

2

Unless I've misunderstood then you should simply be able to alter your procedure to the following

DELIMITER //
  CREATE PROCEDURE getnames(IN id_in INT)
    BEGIN
      select name from people where id>id_in;
    END //
DELIMITER ;

This will simply return the results of the select query, in this case the set of names.

answered Oct 28 '11 at 09:54

whudson05

469
1
3
9

I found an article wich stated that one can use cursors...but it was for Oracle... – ka_lin Oct 28 '11 at 11:35

score 1 · Answer 2 · edited May 23 '17 at 12:19

The problem here is that MySQL only allows table column data types in stored procedures. Sadly this does not include any kind of array, list or collection type.

A similar question has been asked before. Have a read of the answers - they might help you. One of them includes passing a comma separated list out in a large VARCHAR data type.

Another alternative to returning the results is to store the results in a 'results table', call the procedure and then reference the results in the 'results table' from the calling code.

score 1 · Answer 3 · answered Oct 28 '11 at 09:59

1

The parameters in MySQL cannot be objects or tables. If you want to return multiple values, then try to fill temporary table or just run SELECT statement within the procedure and read dataset in the application.

answered Oct 28 '11 at 09:59

Devart

119,203
23
166
186

MySQL Procedure multiple results

3 Answers3