MYSQL Error #1415 on Function Creation: Not allowed to return a result set from a function-CodePudding

Trying to create a conditional-based function that will return a result. I don't if it's the way I am setting the result value that is causing the error? Making MySQL throw the error code 1415 Not allowed to return a result set from a function.

DELIMITER $$

CREATE FUNCTION GetTechFull ( table_flag INT,person_pk CHAR(11) )
RETURNS INT

BEGIN

    DECLARE firstName VARCHAR(64);
    DECLARE lastName VARCHAR(64);
    DECLARE outputRes VARCHAR(64) DEFAULT NULL;

    IF table_flag IS NULL OR person_pk IS NULL THEN
        RETURN NULL;
    END IF;
    IF table_flag = 1 THEN
        SELECT CONCAT(LEFT(ResFirstName,1), " ", ResLastName) as name,ResPhone as telephone, TPGText as pay_grade FROM cpts451_secretproject_rds.ww_techfull;
        SET outputRes =  CONCAT(LEFT(firstName,1), " ", lastName);
    END IF;
    IF table_flag = 0 THEN
        SELECT stdFirstName,stdLastName INTO firstName,lastName FROM student WHERE student.stdNo = person_pk;
        SET outputRes =  CONCAT(LEFT(firstName,1), " ", lastName);
    END IF;
    RETURN outputRes;
END$$

DELIMITER ;

CodePudding user response：

You cannot use common SELECT in a function which sends its result to the output stream. You must use SELECT .. INTO {variables list}.

You may do not use intermediate variable and apply RETURN (SELECT {output column/expression} FROM ... WHERE ... ORDER BY ... LIMIT 1). The parenthesis prevents the output to be sent to the output stream and converts it to scalar value. ORDER BY LIMIT 1 usage is strongly recommended in this case, even when 1-row output is guaranteed now - it may become incorrect in future...

Your function does not process table_flag value other than NULL, 0 or 1 (which can be used, even errorneously). I'd recommend you to use something like

CASE table_flag WHEN 0 
                THEN RETURN ( {query 1} );
                WHEN 1 
                THEN RETURN ( {query 2} );
                ELSE RETURN NULL;
END CASE;

CodePudding user response：

your code has multiple problems, but the bggest ist that you a using a "normal select, which would return a result set, which is not allowed.

so oyu can only use, SELECT .. INTO..FROM..WHERE to get rid of the error message.

Iyour return Value doesn't correspond with the variable 'outputResthey must be f the same datatype

MySQL 8 also wants a DETERMINIsTIC added

Below you see a working code sample, so that you can go from here, to whereever you want

CREATE tABLE student(stdNo int, stdFirstName VARCHAR(64), stdLastName VARCHAR(64))

INSERT INTO student VALUES(1,'test2','testlast')

CREATE TABLe ww_techfull(ResNo int, ResFirstName VARCHAR(64), ResLastName VARCHAR(64)
,ResPhone varchar(16),TPGText varchar(64))

INSERT INTO  ww_techfull VALUES(1,'testfrist', 'Testlast','012345656778','Bad')

CREATE FUNCTION GetTechFull ( table_flag INT,person_pk CHAR(11) )
RETURNS CHAR(64) DETERMINISTIC

BEGIN

    DECLARE firstName CHAR(64);
    DECLARE lastName CHAR(64);
    DECLARE telephone CHAR(64);
    DECLARE pay_grade CHAR(64);
    DECLARE outputRes CHAR(64) DEFAULT NULL;

    IF table_flag IS NULL OR person_pk IS NULL THEN
        RETURN NULL;
    END IF;
    IF table_flag = 1 THEN
        SELECT LEFT(CONCAT(LEFT(ResFirstName,1), " ", ResLastName),64) 
        ,ResPhone  , TPGText 
        INTO outputRes
        ,telephone,  pay_grade 
        FROM ww_techfull WHERE ResNo = person_pk;
        SET outputRes =  LEFT(outputRes,64);
    END IF;
    IF table_flag = 0 THEN
        SELECT LEFT(CONCAT(LEFT(stdFirstName,1), " ",stdLastName),64) INTO outputRes 
        FROM student WHERE student.stdNo   = person_pk;
        SET outputRes =  LEFT(outputRes,64);
    END IF;
    RETURN outputRes;
END

SELECT GetTechFull(0,1)

| GetTechFull(0,1) |
| :--------------- |
| t testlast       |

SELECT GetTechFull(1,1)

| GetTechFull(1,1) |
| :--------------- |
| t Testlast       |

db<>fiddle here