mysqlfunctionmysql-function

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


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';

Solution

  • 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).