Re: circumventing limitations on the "Liimit" clause
Posted by: Paul Medynski
Date: January 08, 2007 09:09PM
Try using a prepared statement instead:
create procedure Foo (
vOffset int unsigned,
vSize int unsigned)
set @offset = vOffset;
set @size = vSize;
prepare FooStmt from
"select substr(ATL_Label,2,6) as part"
"from Label "
"order by part "
"limit ? ?";
execute FooStmt using @offset, @size;
deallocate prepare FooStmt;
*Disclaimer*: I just typed that in by hand, so the syntax might not be 100% correct. It's close though :)
You can even build the statement into a user variable rather than specifying it as a literal string all at once. I've done this to add optional where clauses based on the presence of procedure arguments.
However, I agree that the restrictive syntax of the limit clause is a total nuisance. In fact, it's probably worthy of a bug, since using prepared statements makes it (likely) impossible for the stored procedure to gain any real compile-time optimizations.