PeCosta - 9 months ago 58

MySQL Question

I have an SQL Function called

`compare_two_regions`

`BIT`

`compare_two_series`

I simplified this function to the point where i only want to return the value from the

`compare_two_regions`

`compare_two_regions`

`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 Source

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.