Re: circumventing limitations on the "Liimit" clause
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
Subject
Views
Written By
Posted
4127
November 13, 2006 05:55PM
2585
November 15, 2006 12:36PM
2460
November 15, 2006 10:06PM
Re: circumventing limitations on the "Liimit" clause
31712
January 08, 2007 09:09PM
3940
January 23, 2007 12:49AM
3175
August 29, 2007 08:25AM
2769
August 29, 2007 11:45AM
3708
August 29, 2007 12:29PM
3358
August 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.