Sunday, June 20, 2010

Workaround: Using LIMIT parameters in MySQL stored procedures

It seems that MySQL doesn't accept direct parameter assignment for the LIMIT clause. For example the stored procedure parameters or the local defined variables will generate syntax error if are used directly in the LIMIT clause inside a Store Procedure: SELECT...LIMIT @limit;

MySQL Store Procedure LIMIT Workaround

A possible worksround for this MySQL LIMIT issue is to use the LIMIT containing query like this:

SET @v_limit = v_store_proc_parameter;
SET @v_query = 'SELECT ... FROM ... WHERE ... LIMIT ?`;

PREPARE v_query from @v_query;
EXECUTE v_query USING @v_limit;