MySQL function return more than 1 row

Question

I'd like to write a MySQL stored function that returns multiple rows of data. Is this possible? It seems to be locked at 1 row -- can't even return 0 rows.

For example

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 a MySQL stored proc though, doesn't return 0 rows.. it returns 1 row with the value null in it.

Can I return 0, or more than 1 row from a MySQL function, how?

Solution 1

From the MySQL reference:

23.4.6: Can MySQL 5.0 stored routines return result sets?

Stored procedures can, but stored functions cannot. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client. You need to use the MySQL 4.1 (or above) client-server protocol for this to work. This means that—for instance—in PHP, you need to use the mysqli extension rather than the old mysql extension.

Solution 2

You want to create a function that returns a table:

create function go()
RETURNS @MyTable table 

then populate that table however...

Insert @MyTable
Values (.....) 

and then return that. It should contain 0, 1, or many rows, depending on whatever you filled it with. (Or didn't fill it with...)