MySQL function doesn't return a value
Posted on 2016-09-13
I have written the MySQL function below, and when I invoke it, the action grid reports 'OK'; but the results grid does not display nor does it report a value.
Here is the FN:
CREATE DEFINER=`mysql`@`%` FUNCTION `GetBOP`(pID bigint, gameid integer) RETURNS int(11)
declare bop int;
/*set @btgpos := 0;*/
set @pID := pID;
set @gameid := gameid;
SELECT myfield INTO bop from mytable where Playerid = @pID and gameid = @gameid;
/*SELECT @btgpos := `myfield` from mytable where Playerid = @pID and gameid = @gameid;*/
If bop > 9 then
set bop = 10;
To execute - I run this:
RESULTS: (In Action grid of MySQL Workbench)
Action - select getBOP(8701,24091) Response - OK
No Results grid displays and no values displayed.
To test: I execute this:
set @pID := 8701;
set @gameid := 24091;
SELECT myfield from mytable where Playerid = @pID and gameid = @gameid;
With this test I get
Result Grid - Results - 8
Action grid - Action - 1 Row returned
It seems like the function is returning a NULL or boolean or ?? Not sure why I'm not getting a result.
As you can see by the function above, I've tried using @btgpos variable vs. declare variable. This causes Error 1415 (can't return a result..) so I am perhaps using it incorrectly.
I've written a very similar fn that returns numbers as expected and I can not see the difference at all. Only difference is it has only one parameter vs two.
Any advice would be greatly appreciated.