MySQL Forums
Forum List  »  Stored Procedures

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)
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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: circumventing limitations on the "Liimit" clause
31712
January 08, 2007 09:09PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.