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

Question

I have write one function but getting this error Not allowed to return a result set from a function

DELIMITER $$

CREATE FUNCTION getTestFunction
(
    p_ParentID int,
    p_ListName nvarchar(50),
    p_Type nvarchar(50),
    p_Count int 
)
RETURNS nvarchar(2000)
BEGIN
    DECLARE p_KeyValue nvarchar(2000);
    DECLARE p_ListValue nvarchar(2000);
    DECLARE p_TextValue nvarchar(2000);
    DECLARE p_ReturnValue nvarchar(2000);
    DECLARE p_Key nvarchar(2000);
    
    IF p_ParentID = 0 THEN
        IF p_Count = 0 THEN
            SET p_ReturnValue = '';
        ELSE
            SET p_ReturnValue = p_ListName;
        END IF;
    ELSE
    
            SELECT  p_KeyValue = ListName + '.' + Value
                
                FROM ListsTable
                WHERE EntryID = p_ParentID  LIMIT 1 ;
    RETURN p_ReturnValue;
            If p_Type = 'ParentKey' Or (p_Type = 'ParentList' AND p_Count > 0) THEN
                SET p_ReturnValue = p_KeyValue;
            ELSE 
                IF p_Type = 'ParentList' THEN
                    SET p_ReturnValue = p_ListValue;
                ELSE
                    SET p_ReturnValue = p_TextValue;
                END IF;
            END IF;

            IF p_Count > 0 THEN
                If p_Count = 1 AND p_Type = 'ParentList' THEN
                    SET p_ReturnValue = p_ReturnValue + ':' + p_ListName;
                ELSE
                    SET p_ReturnValue = p_ReturnValue + '.' + p_ListName;
                END IF;
            END IF;
        
    END IF; 
    RETURN p_ReturnValue;
END$$
DELIMITER ;

Answer 1

You want to assign the result of a query to a variable, but in fact you're just selecting. That's why MySQL's complaining.

You have to change this

            SELECT  p_KeyValue = ListName + '.' + Value
            FROM ListsTable
            WHERE EntryID = p_ParentID  LIMIT 1 ;

to

            SELECT CONCAT(ListName, '.', `Value`)
            INTO p_KeyValue
            FROM ListsTable
            WHERE EntryID = p_ParentID  LIMIT 1 ;

And you should add an ORDER BY. A LIMIT without ORDER BY doesn't make sense, since there's no guaranteed order in a relational database.

Answer 2

Mysql complains about SELECT statement in your function,

probably it understands SELECT p_KeyValue = ListName + '.' + Value as comparison

change it to

SELECT CONCAT(ListName, '.', Value) INTO p_KeyValue