MySQL function returns more than 1 row

Question

I would like to write a MySQL stored function that returns multiple rows of data. Is it possible? It seems to be locked in 1 row - cannot even return rows 0 .

for instance

  DELIMITER //

 create function go ()

 RETURNS int
 deterministic
 NO SQL

 BEGIN

 return null;  - this doesn't return 0 rows!  it returns 1 row
 - return 0;

 END //

 DELIMITER;

Returning null from the MySQL stored procedure, although it does not return 0 rows .. it returns 1 row with a null value in it.

Is it possible to return 0 or more than 1 row from a MySQL function, like?

Answer 1

From the MySQL link:

23.4.6: Can MySQL 5.0 stored procedures return result sets?

Stored procedures can, but stored functions cannot. If you perform a regular SELECT inside a stored procedure, the result set is returned directly to the client. For this you need to use the MySQL client-server protocol 4.1 (or higher) for this. This means that, for example, in PHP, you need to use the mysqli extension, not the old mysql extension.

Answer 2

You want to create a function that returns a table:

 create function go() RETURNS @MyTable table 

then fill out this table however ...

 Insert @MyTable Values (.....) 

and then return it. It should contain 0, 1 or many lines, depending on what you filled it with. (Or didn’t fill it ...)