I have to try using variable value on MySQL Query Procedure.
But it doesn't work. Here is my procedure:
SET @start=0;
SET @finish=150;
...
DECLARE curs1 CURSOR FOR SELECT tempDate FROM tbl_temp LIMIT @start,@finish;
...
I just want to create query like below
SELECT tempDate FROM tbl_temp LIMIT 0,150;
So guys, what should I do?
Thanks.
you can use it with dynamic query like below
@qry varchar(max)
set @qry ='DECLARE curs1 CURSOR FOR SELECT tempDate FROM tbl_temp LIMIT'+ @start+','+ @finish
exec(@qry)
Using variables or procedure parameters in LIMIT clause is available in MySQL 5.5.
Othervise you need to use prepared statements. From the documentation - Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6. Example:
SET @skip=1; SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;
More information - SELECT syntax.
Try this :
SET start=0;
SET finish=150;
DECLARE curs1 CURSOR FOR SELECT tempDate FROM tbl_temp LIMIT start,finish;
Currently, MySQL doesn't support variable on LIMIT clause as mentioned at http://bugs.mysql.com/bug.php?id=8094
However, you can use prepared statement to solve your problem as script below:
SET @start=0;
SET @finish=150;
SET @query = "SELECT tempDate FROM tbl_temp LIMIT " + @start + ", " + @finish
EXEC(@query)
