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
– Kasador, Commented Apr 25, 2018 at 23:12
Where do you expect this message to be printed if you're not using MySQL interactively? — Barmar
– Barmar, Commented Apr 25, 2018 at 23:16

Paul Campbell · Accepted Answer · 2018-04-25 23:15:43Z

1

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

answered Apr 25, 2018 at 23:15

Paul Campbell

1,9862 gold badges14 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related