Back to Top

How To pass limits with store procedure in MySQL

to-pass-limits-with-store-procedure-in-mysql

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

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

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:

CREATE PROCEDURE GetProducts(in id INT, in offsets INT, in limits INT)
BEGIN
	SET @limit_sp = CONCAT(' LIMIT ', offsets , ',', lmits);
	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)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

An Introduction To NoSQL and MongoDB

Posted on 7 years ago

Bhumi

Hello world

Updated 1 week ago

Nick Carter

In depth Understanding of Laravel Core

Updated 2 months ago

Bhumi

ECMAScript 6 New Features – 2

Updated 1 year ago

Bhumi

The Reader’s Poll – June 2015

Posted on 4 years ago

Bhumi

The Reader’s Poll – August 2014

Posted on 4 years ago

Bhumi

The Reader’s Poll – June 2014

Posted on 5 years ago

Bhumi

The Readers’ Poll – May 2014

Posted on 5 years ago

Bhumi

The Readers’ Poll – August 2013

Posted on 5 years ago

Bhumi