MySQL create function dfficulties

Question

Okay, so this is my first time attempting to CREATE FUNCTION, so please bear with me!

So, I have this (simplified) table structure:

CREATE TABLE `pokemon` (
 `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
 `userid` INT UNSIGNED NOT NULL,
 `boxid` TINYINT UNSIGNED NOT NULL DEFAULT 255,
 `boxpos` SMALLINT UNSIGNED,
 PRIMARY KEY (`id`),
 UNIQUE KEY `position` (`userid`,`boxid`,`boxpos`)
) ENGINE=InnoDB

I want to create a function that, given a user ID and box ID, returns an empty box position (ie. one that won't trigger a duplicate key error).

Note that boxid uses values from 0 to 249 for box IDs, and 255 as a special value for the user's Party. boxpos can range from 0 to 45,504 in Boxes, but only 0 through 5 in the Party. Also, in the Party the returned position should be the first empty one, whereas in a Box it should be a random position.

So, with all this in mind, here was my attempt:

begin
declare ret smallint unsigned;
declare exist tinyint unsigned default 1;
if fieldid = 255 then
 create temporary table `party` (
 `pos` smallint unsigned not null
 );
 insert into `party` values (0),(1),(2),(3),(4),(5);
 delete from `party` where `pos` in (select `fieldpos` from `pokemon` where `userid`=userid and `fieldid`=255);
 select `pos` into ret from `party` limit 1;
 if ret is null then select `[[Error: No room in Party]]` from `party`; end if;
else
 while exist=1 do
 set exist=0;
 set ret=floor(rand()*45504);
 select 1 into exist from `pokemon` where `fieldid`=fieldid and `userid`=userid and `fieldpos`=ret;
 end while;
end if;
return ret;
end;

(Note that this is the body of the function, as entered into phpMyAdmin)

EDIT: I've fixed the DECLARE problem, but now it's saying I can't return a result set from a function.

I'm unsure where I've gone wrong, and I think I need help getting on the right track. In particular, have I got the function logic right in the first place?

Answer 1

In this line:

if ret is null then select `[[Error: No room in Party]]` from `party`; end if;

you select a constant value for all rows from the party table but don't put the result of that select into a variable. That's probably where the error comes from.

It should probably be something like:

if ret is null then 
 set ret = '[[Error: No room in Party]]';
end if;

(Also note that String literals need to be enclosed in single quotes ('), not with those dreaded backticks - which aren't necessary in the first place, so it's better to leave them out alltogether).

• Niet the Dark Absol: The point of that line is to throw an error, since I'm using a version of MySQL prior to 5.5. I need the function to interrupt the query being run if there is no room in the Party.
• user330315: @Kolink: Doesn't MySQL support throwing exceptions?
• user330315: @Kolink: that's lame (but I'm not really surprised...). Maybe you can execute dynamic SQL that is invalid (using PREPARE) so that the SQL is not checked when creating the procedure.
• Niet the Dark Absol: I was able to fix it by adding into exist. That way the query result is being put in a variable.