Back to Top

How 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

When I tried to store procedure, I got error something like this

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:

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

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

Getting Started with CodeIgniter

Posted on 7 years ago


Two years blogiversary

Posted on 5 years ago


How To Create a Temporary File in PHP

Posted on 6 years ago


How To Change Database PREFIX in WordPress

Posted on 6 years ago


ECMAScript 6 New Features – 2

Posted on 10 months ago