Line of code of the day #5

Another LOCOTD.... just for me to remember those nice *cough cough* code lines that i come around from time to time.... i'm currently working as a trainee (until February), i'm currently implementing a custom featured content management system (CMS), i'm using php, mySQL, xhtml, css and a bit of JS for some slideshows... but this LOCOTD is about SQL ...
Store Procedures (SPs), as you may not know, are supported by mySQL for quite some time, however not quite perfectly as you might expect.... On SPs, you basically build a wrap box around one or more SQL statements, you can set variables, use the usual SQL stuff.... in my case i was using a SELECT, like i did for the rest of the dozens of SPs i did before, i was implementing a page like feature in the CMS, for that LIMIT clause is god send, but guess what.... in SPs you can't use non-constant integers in LIMIT parameters... yes.... you can use the arguments of the SP anywhere, except on the LIMIT clause...
So ... "How to solve it?" that would be the question... one way would be using a prepared statement, like this:
CREATE PROCEDURE `fooTable`.`sp_fooTableList` ( limit_arg int ) 
BEGIN
SET @sql = concat('SELECT * FROM `fooTable` LIMIT ', limit_arg); 
PREPARE STMT FROM @sql;
EXECUTE stmt; 
END

An obvious problem with this, is that it broken the extra security provided by SPs... but it is a valid workaround.... quite simple as well.... but because this prepared statement kinda defeat the purpose of having a nicely wrapped SQL statement, i found a way of doing it without recurring to it, i say found and not invented, credit goes to Gert Brigsted, it's easy to follow and understand how and why it works: 
CREATE PROCEDURE `fooDB`.`sp_fooTableList` ( limit_start, limit_end )
BEGIN
SET @rownum:=0;
SELECT *
FROM ( 
  SELECT ( @rownum:=@rownum+1) AS Rownumber, `fooTable`.* 
  FROM `fooTable` 
  ORDER BY `fooTable`.`mooCol` DESC
) AS t
WHERE Rownumber > limit_start AND Rownumber <= limit_end;
END

It looks messy, but it works... sure, it has some impact on performance, but that's the price to pay for security... until LIMIT clause accepts variables as parameters.

Perhaps you are thinking, "That surely is, or will be, fixed in current iterations of mySQL" ... you are right it is, in versions 5.5.6 and 6.0.14, it is 'fixed' ... But not all servers allow you to change mySQL version installed... so, I guess you may run into this problem and have to use one of the above workarounds...
As a side note, this was submitted as bug #11918 by 13 July 2005 and 'solved' by 24 September 2010 .... more than 5 years to 'fix' this 'bug' ... i use quotes because wasn't exactly a bug but more like a non-feature...
Hope it helps someone out there bashing at "ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near: s_limit, e_limit;"