how to use dynamic query inside a function

i’m trying to create a function GETUSERPROP(prop, filter_prop, filter_value) that selects one item in database filtering by property and value

SET @_GET_PROP      = "name";
SET @_FILTER_PROP   = "id";
SET @_FILTER_VALUE  = "15";

SET @q = CONCAT(
    ' SELECT ', 
    @_GET_PROP,
    ' FROM MYVIEW '
    ' WHERE 1 ',    
    ' AND (`', @_FILTER_PROP, '` = "', @_FILTER_VALUE, '") '
);
PREPARE stmt FROM @q;
EXECUTE stmt;

the query outside function is running fine, but when I try to put this inside a function, I get the error

mysql dynamic sql is not allowed in stored function or trigger

I need this as a function to use inside other queries, like this:

SELECT 
    GETUSERPROP("name", "id", "15") AS name,
    GETUSERPROP("age",  "id", "15") AS age

Answer

From error message it is obvious we cannot use dynamic sql in function. Dynamic sql is only possible in procedure. So you have to think in alternative. If number of combination is not too much then you can use IF statement for each possible combination for example.

IF @_GET_PROP = 'name' AND @_FILTER_PROP = 'ID' THEN
  SELECT name from MYVIEW where id = @_FILTER_VALUE;
ELSE IF @_GET_PROP = 'Agen' AND @_FILTER_PROP = 'ID' THEN
  SELECT name from MYVIEW where id = @_FILTER_VALUE;

……………. ……………

and so on..

or something like this

SELECT CASE @_GET_PROP WHen 'name' then name when 'id' then ID when 'Age' then Age END from MYVIEW where 
 ((@_FILTER_PROP = 'ID' AND id = @_FILTER_VALUE)
 or (@_FILTER_PROP = 'name' AND name = @_FILTER_VALUE)
or (@_FILTER_PROP = 'age' AND age = @_FILTER_VALUE)
) ;