Today I faced a strange problem that is to apply LIMIT in store procedure in MySQL. I have created one store procedure in MySQL and I want to apply pagination in Store Procedure so I have used two parameter Offset and Limit and apply into the query.
This Store procedure was working fine in local MySQL, but it was not working on the live server. For few minutes, my mind was blank and my first doubt was the version of mysql used in a local and live server so I checked it.Yes, the live server has older version whereas local has the latest version of MySQL.Then I have made a quick search and found the answer for the same.
Let’s see Store Procedure I have created in local MySQL.In this, I need to pass in two parameters for the LIMIT value so tried something like this
1 2 3 4 5 6 | CREATE PROCEDURE GetProduct(in id INT, in offsets INT, in limits INT) BEGIN SELECT * FROM product ORDER BY name Where pid =id LIMIT offsets,limits; END |
When I tried to store procedure, I got error something like this
1 2 3 | ERROR: #1064 - 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 …... |
The stored procedure fails to create while the LIMIT is available in the query. If I remove the LIMIT from the procedure, It was worked fine.
The problem is that the number with LIMIT must allow integer-valued parameters and LIMIT cannot be parametrized in MySQL stored procedures which is one kind of one bug in MySQL.
Then, I have done changes in Store Procedure so It allows to work using variables in MySQL 5.0.15 and earlier versions with LIMIT value.
Let’s have a look into it:
1 2 3 4 5 6 7 8 9 10 11 12 | CREATE PROCEDURE GetProducts(in id INT, in offsets INT, in limits INT) BEGIN SET @query = CONCAT( "SELECT * FROM product Where pid =",id," ORDER BY name"); ORDER BY name"); SET @query = CONCAT(@query, @limit_sp); PREPARE stmt FROM @query; EXECUTE stmt ; DEALLOCATE PREPARE stmt ; END |
Here, I have used PREPARE statement to execute the query and concat my parameters with query and query is passed as the string within CONCAT.MySQL is not allowed LIMIT value as the parameter that’s why I used concat operation in creating the procedure.
You may like:
To use Store Procedure with PDO
MySQL CURSOR Explained
UDF in MySQL
I hope you have enjoyed this article. Don’t Forget to Follow us on Twitter or Subscribe us to Get the Latest Updates.
Comments (1)