MySQL error, "Not allowed to return a result set from a function"?

Question

I'm trying to wrap my head around functions in mySQL and I'm currently making one that checks the column account_description and it's value to see if the description already exists.

If it does exist already, display a message saying so. However, if the description is not there, display a different message saying that it is not found.

Thanks!

MySQL Code:

DROP FUNCTION IF EXISTS test_glaccounts_description

DELIMITER //

CREATE FUNCTION test_glaccounts_description
(
    check_description VARCHAR(50)
)
RETURNS VARCHAR(50)
BEGIN

DECLARE var_check VARCHAR(50);

SELECT
    account_description INTO var_check
FROM
    general_ledger_accounts
WHERE
    account_description = check_description;

    IF var_check = check_description THEN
        SELECT 'That description already exists.';
    ELSEIF var_check != check_description THEN
        SELECT 'That description does not exist.';
    END IF;

RETURN var_check;

END //

DELIMITER ;

SELECT
    test_glaccounts_description(account_description) as 'Check'
FROM 
    general_ledger_accounts
WHERE
    account_description = 'Accounting';

@Eric MySQL has a `SELECT INTO`. You can use this in stored procedures too. Like, `SELECT column_name INTO my_variable`. As I declared my variable before. — Kasador, Apr 25 '18 at 23:12
Where do you expect this message to be printed if you're not using MySQL interactively? — Barmar, Apr 25 '18 at 23:16

score 1 · Accepted Answer · answered Apr 25 '18 at 23:15

You can't use a SELECT to display the message in a stored function, you are restricted to returning a single value via the RETURN statement. You'll find this covered in the documentation

Statements that return a result set can be used within a stored procedure but not within a stored function. This prohibition includes SELECT statements that do not have an INTO var_list clause and other statements such as SHOW, EXPLAIN, and CHECK TABLE. For statements that can be determined at function definition time to return a result set, a Not allowed to return a result set from a function error occurs (ER_SP_NO_RETSET). For statements that can be determined only at runtime to return a result set, a PROCEDURE %s can't return a result set in the given context error occurs (ER_SP_BADSELECT).

MySQL error, "Not allowed to return a result set from a function"?

1 Answers1