Alternatives to dynamic sql in stored function

Question

I'm writing a stored function where I calculate the position of a cell, which value I need to select from a table. To do this, I decided to save this position in a variable, in order to use it as the offset of a LIMIT clause.

According to my research, the way of using values set into local variables with the LIMIT statement is through a prepared statement, but I also got that prepared statements (nor any dynamic SQL) are allowed in stored functions. Are there any alternatives to solve my problem?

A simplified example of my situation:

CREATE FUNCTION foo(a int) RETURNS decimal DETERMINISTIC
BEGIN
     SET @var1 := (SELECT COUNT(*) FROM table);
     SET @var2 := (ROUND(@var1 * a/5))
     PREPARE STMT FROM 'RETURN (SELECT * FROM other_table LIMIT ?, ?)';
     EXECUTE STMT USING @var2, @var1;
END
$$ DELIMITER ;

Ideally, this would get me the result I need, where I need it. But, of course, I get Error Code 1336 saying "Dynamic SQL is not allowed in stored function or trigger"

Answer 1

You don't need dynamic SQL for this stored function. You don't need to use dynamic SQL for a LIMIT clause. You just need to make sure the variables are INT type, not strings.

Here's a quick demo:

create function foo(a int) returns int reads sql data 
begin 
  return (select x from test limit 1 offset a); 
end

Notice several other things:

• Use READS SQL DATA instead of DETERMINISTIC. Your function is not deterministic. You should read the manual page on create function to understand these options better.
• Don't use SELECT *. A function can only return a single scalar value, not a set of columns. The table you are querying might in fact have one column, but it's a good habit to make queries be more clear.
• Using LIMIT with no ORDER BY may surprise you later, because it doesn't guarantee which order it will use for determining the offset. It's best if you use ORDER BY explicitly.
• When using LIMIT, I think it's more clear to use LIMIT <count> OFFSET <offset> instead of LIMIT <offset>, <count>. They do the same thing, but it's easier to remember which argument is which.
• Your LIMIT query appears to be selecting many rows. You need the query to select exactly one column and one row, or else it's not valid to return from a stored function.