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

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

Answer

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