Using variables as OFFSET in SELECT statments inside mysql's stored functions

Question

Hi,

I'm quite new to subject of writting stored function for mySQL database, hence i'm not sure if what i'm trying to do here is possible at all.

I need a function that will return a column from random row from a table. I don't want to use ORDER BY RAND() method to do that, rather i would do this like this:

DECLARE MAX_COUNT INT DEFAULT 120000;
DECLARE rand_offset INT;
DECLARE str_rnd_word VARCHAR(255);
SET rand_offset = FLOOR((RAND() * MAX_COUNT));

SELECT word INTO str_rnd_word FROM all_words LIMIT 1 OFFSET rand_offset ;

RETURN str_rnd_word;

MySQL throws an error upon creating function with body like that. But when I use hard-coded number as OFFSET it works just fine.

Can someone shed some light on the subject please.

I'm running MySQL 5.0.45 on windows box.

Thanks

Answer 1

You can't put a variable into the LIMIT clause in MySQL stored procedures. You have to interpolate it into a string and then execute the string as a dynamic query.

SET rand_offset = FLOOR(RAND() * (SELECT COUNT(*) FROM all_words));
SET sql = CONCAT('SELECT word INTO str_rnd_word FROM all_words LIMIT 1 OFFSET ', rand_offset);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

Answer 2

Execute doesn't work in a stored function.