stored procedures - MySQL said: #1415 - Not allowed to return a result set from a function

Question

Please help me to solve this Error

While creating stored function in MySQL's PHPMyAdmin

MySQL said: #1415 - Not allowed to return a result set from a function

BEGIN
    SET @pAvg = 0; 
    SET @p1 = 0;
    SET @p2 = 0;
    SET @p3 = 0;
    SET @p4 = 0;
    IF river > 1 THEN
        SELECT @P1 := Precipitation FROM abd_2013 where id = 1;
        SELECT @P2 := Precipitation FROM abd_2014 where id = 1;
        SELECT @P3 := Precipitation FROM abd_2015 where id = 1;
        SELECT @P5 := Precipitation FROM abd_2016 where id = 1;
        SELECT @pAvg := (@p1 + @p2 + @p3 + @p4)/4;
     ELSEIF river < 1 THEN SELECT @P5;
     END IF;
     RETURN river;
END

Everything going fine if I do the same in Stored Procedure but whenever I opt FUNCTION it gives warning add RETURN and after writing return it raises error #1415

MySQL said: #1415 Error

Answer 1

The error message in this case tells the most important part:

Not allowed to return a result set from a function

This behaviour is consistent with what's documented in MySQL manual on stored procedures and functions:

For statements that can be determined at function definition time to return a result set, a Not allowed to return a result set from a function error occurs (ER_SP_NO_RETSET).

You assign values to your @Pn variables using select statements that return a resultset and this is not allowed in a function. You have to remove these statements from your code. RETURN river returns a result value, but not a result set.

I'm also worried that you use session variables (variables defined as @variable_name) which are shared across a connection, so potentially multiple call to the same function at the same time within a connection may interfere with each other.

A stored function is only supposed to return a single value as its output in with the return statement. Anything else is considered as a side effect. If you want your MySQL script to populate multiple variables, then you must use a stored procedure, you cannot use a stored function.