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

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
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 *

Most Popular Posts

How to use jQuery UI tabs in a WordPress?

Posted on 4 years ago

Bhumi

How do I add items in Top navigation bar?

Posted on 4 years ago

Bhumi

Newly Approved Joomla Module

Posted on 4 years ago

Bhumi

Overview of ECMAScript 6 new Features

Updated 18 years ago

Bhumi

Getting started with Amazon Web Services

Updated 18 years ago

Bhumi

Facade implementation in Laravel

Updated 18 years ago

Bhumi

The Reader’s Poll – June 2015

Posted on 2 years ago

Bhumi

The Reader’s Poll – August 2014

Posted on 3 years ago

Bhumi

The Reader’s Poll – June 2014

Posted on 3 years ago

Bhumi

The Readers’ Poll – May 2014

Posted on 3 years ago

Bhumi

The Readers’ Poll – August 2013

Posted on 4 years ago

Bhumi