A while back, someone asked if a stored procedure could take a
parameter that can be used within a statement as a value for LIMIT.
I have a solution using Prepared Statements and placeholders. Prepared
Statements with placeholders are available from 5.0.7.
Anyway, here goes using the standard MySQL "world" database as an example:
DELIMITER //
CREATE PROCEDURE list_cities (IN city_limit INT)
BEGIN
SET @city_limit = city_limit;
PREPARE STMT FROM 'SELECT * FROM City LIMIT ?';
EXECUTE stmt USING @city_limit;
END
//
DELIMITER ;
mysql> call list_cities(1);
+----+-------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call list_cities(5);
+----+----------------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------------+-------------+---------------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
+----+----------------+-------------+---------------+------------+
5 rows in set (0.00 sec)
Not sure how useful that is. Maybe it is useful if one is frequently
querying a certain table when working on the command line?
Imran Chaudhry
--
http://www.atomdatabase.com
MySQL Database Management & Design Services
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
