Skip navigation links

MySQL Forums :: Stored Procedures :: circumventing limitations on the "Liimit" clause


Advanced Search

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
circumventing limitations on the "Liimit" clause 2003 Dewey Gaedcke 11/13/2006 05:55PM
Re: circumventing limitations on the "Liimit" clause 1517 Bob Field 11/15/2006 12:36PM
Re: circumventing limitations on the "Liimit" clause 1424 Dewey Gaedcke 11/15/2006 10:06PM
Re: circumventing limitations on the "Liimit" clause 9659 Paul Medynski 01/08/2007 09:09PM
Re: circumventing limitations on the "Liimit" clause 2276 Dewey Gaedcke 01/23/2007 12:49AM
Re: circumventing limitations on the "Liimit" clause 1913 Jo H. 08/29/2007 08:25AM
Re: circumventing limitations on the "Liimit" clause 1834 Peter Brawley 08/29/2007 11:45AM
Re: circumventing limitations on the "Liimit" clause 2241 William Chiquito 08/29/2007 12:29PM
Re: circumventing limitations on the "Liimit" clause 2051 Jo H. 08/29/2007 03:07PM


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.