PeCosta PeCosta - 22 days ago 11
MySQL Question

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

I have an SQL Function called

compare_two_regions
that returns the datatype
BIT
, I want to call this function on a function called
compare_two_series
.
I simplified this function to the point where i only want to return the value from the
compare_two_regions
function. Still I would like to call the
compare_two_regions
function under a loop, but even in this simplified case:

DELIMITER $$

create function compare_two_series( serieA varchar(255), serieB varchar(255))
returns BIT
begin
declare result BIT;

/*falta ver*/
SELECT P1.x1,P1.y1,P1.x2,P1.y2,P2.x1,P2.y1,P2.x2,P2.y2
FROM region as P1, region as P2
WHERE P1.region.series_id = serieA AND P2.region.series_id = serieB;

SELECT compare_two_regions(P1.x1,P1.y1,P1.x2,P1.y2,P2.x1,P2.y1,P2.x2,P2.y2) into result;
return result;
end$$
DELIMITER ;


I get the following error:
Not allowed to return a result set from a function

Answer

You can try changing this

SELECT P1.x1,P1.y1,P1.x2,P1.y2,P2.x1,P2.y1,P2.x2,P2.y2
FROM region as P1, region as P2
WHERE P1.region.series_id = serieA AND P2.region.series_id = serieB;

SELECT compare_two_regions(P1.x1,P1.y1,P1.x2,P1.y2,P2.x1,P2.y1,P2.x2,P2.y2) into result;

to this

SELECT compare_two_regions(P1.x1,P1.y1,P1.x2,P1.y2,P2.x1,P2.y1,P2.x2,P2.y2) into result
FROM region as P1, region as P2
WHERE P1.region.series_id = serieA AND P2.region.series_id = serieB;

It will fail if the result of the query returns more than one row. If you want to use a loop, you will need to set up a cursor.