Re: circumventing limitations on the "Liimit" clause

[Paul Medynski]

Try using a prepared statement instead:

create procedure Foo (
vOffset int unsigned,
vSize int unsigned)
begin
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;
end;

*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.

-Paul