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