How can I return the result of a select in a function in MySQL?

Question

I'm a student and I'm trying to get my function to return the result of my select, but I don't know what to put in the RETURN.

I have tried to create functions with mathematical operations for example, and it works correctly for example:

DELIMITER $$
CREATE FUNCTION `getScore`(`a` INT, `b` INT, `c` INT) RETURNS INT
BEGIN
    RETURN a + b + c;
END$$
DELIMITER ;

SELECT getScore(0.3, 0.4, 0.5);

But my problem is in this function, I don't know what to put in the RETURN or how to scrutinize the function:

DELIMITER $$

CREATE FUNCTION `getCharacters`() RETURN 
BEGIN

        SELECT * 
        FROM `characters` 
        WHERE `level` > 50

END$$

DELIMITER ;

SELECT getCharacters();

I have the next error:

A "RETURNS" keyword was expected. (near "RETURN" at position 34)

Thank you for any help and excuse me for my English.

score 12 · Accepted Answer · answered Jan 09 '19 at 21:35

12

A MySQL function can only return a single value. You can use a stored procedure, instead:

DELIMITER $$

CREATE PROCEDURE `getCharacters`() 
BEGIN

        SELECT * 
        FROM `characters` 
        WHERE `level` > 50;

END $$

DELIMITER ;

CALL getCharacters;

More info about the difference between stored procedures and functions can be found here: MySQL stored procedure vs function, which would I use when?

answered Jan 09 '19 at 21:35

Albert

211
1
6

may we use a function to return something like this? RETURN COUNT(*) FROM products; – capovawi Nov 02 '22 at 16:25

score 4 · Answer 2 · answered Jan 09 '19 at 21:20

MySQL stored functions only return a single scalar value. They cannot return result sets. Functions can be used in a scalar expression context.

You can use a stored procedure to return a result set, but you can't use it in an expression. You can only run a procedure with CALL.

How can I return the result of a select in a function in MySQL?

2 Answers2