Per: William Chiquito
http://forums.mysql.com/read.php?98,144558,144558#msg-144558
Passing dynamic variables in a stored procedure works per his example. I tired it out myself and all is well.
SAMPLE CODE (last two variables are the ones of interest):
IN model_1 varchar(40), IN model_2 varchar(40), IN model_3 varchar(40), IN filter varchar(20), IN p_low int, IN p_high int
)
BEGIN
IF model_1 = '' AND model_2 = '' AND model_3 = '' AND make_1 = '' AND make_2 = '' AND make_3 = '' THEN
SET @a = CONCAT('SELECT * FROM vehicleinventory ORDER BY ', filter, ' DESC LIMIT ', p_low, ',', p_high);
PREPARE stmtl FROM @a;
EXECUTE stmtl;
DEALLOCATE PREPARE stmtl;
You can also set variables as in:
IN model_1 varchar(40), IN model_2 varchar(40), IN model_3 varchar(40), IN filter varchar(20), IN p_low int, IN p_high int
)
BEGIN
SET @h = 10;
IF model_1 = '' AND model_2 = '' AND model_3 = '' AND make_1 = '' AND make_2 = '' AND make_3 = '' THEN
SET @a = CONCAT('SELECT * FROM vehicleinventory ORDER BY ', filter, ' ASC LIMIT ', @h, ',', p_high);
PREPARE stmtl FROM @a;
EXECUTE stmtl;
DEALLOCATE PREPARE stmtl;
Aslightly different syntax from the the MySQL manual:
http://dev.mysql.com/doc/refman/5.0/en/select.html
For prepared statements, you can use placeholders (supported as of MySQL version 5.0.7). The following statements will return one row from the tbl table:
SET @a=1;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE STMT USING @a;
The following statements will return the second to sixth row from the tbl table:
SET @skip=1; SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;
I am happy...for now : )
Edited 2 time(s). Last edit at 04/11/2007 04:06PM by Matthew Grdinic.