Mysql error: Not allowed to return a result set from a function

Question

I am trying to have a conditional change in a parameter for update statement. I am getting the following error when I try the following function

/home/y/bin/mysql -u root < testpri.sql > out
ERROR 1415 (0A000) at line 4: Not allowed to return a result set from a function

Contents of testpri.sql are as follows:

use `zestdb`;
DROP FUNCTION IF EXISTS UPDATEPASSWD;
DELIMITER //
CREATE FUNCTION UPDATEPASSWD(n INT) RETURNS varchar(255) DETERMINISTIC 
BEGIN
    DECLARE mypasswd varchar(255);
    IF (n = 1) THEN
       SET mypasswd = '12ccc1e5c3c9203af7752f937fca4ea6263f07a5';
       SELECT 'n is 1' AS ' ';
    ELSE
       SET mypasswd = '1a7bc371cc108075cf8115918547c3019bf97e5d';
       SELECT 'n is 0' AS ' ';
    END IF;>
    SELECT CONCAT('mypasswd is ', mypasswd) AS ' ';
    RETURN mypasswd;
END //
DELIMITER ;
CALL UPDATEPASSWD(0);

What am I missing?

Paul Roub · Accepted Answer · 2013-05-11 01:56:51Z

2

I think it's actually your debugging SELECT calls.

From the docs:

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

answered May 11, 2013 at 1:56

Paul Roub

36.5k27 gold badges88 silver badges95 bronze badges

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

Comments

David A. Gray · Accepted Answer · 2018-03-04 19:58:45Z

0

I arrived in search of answers to the same question, and found another way to work around the issue, so that I can use the SELECT statement that is the heart and soul of the MySQL function that elicited the warning.

Consider the following snippet.

SET intNMatches = ( SELECT COUNT(*) ...

SET coerces the SELECT statement to return its one and only column, a row count, into intNMatches, a local variable cast to BIGINT. Since it contains trade secrets, I can't show the rest of the query. Suffice it to say that the query installs without causing the MySQL engine to issue a warning.

answered Mar 4, 2018 at 19:58

David A. Gray

1,0951 gold badge12 silver badges20 bronze badges

Collectives™ on Stack Overflow

Mysql error: Not allowed to return a result set from a function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related