Create Dynamic Query In MYSQL Using Stored Procedure

Posted on by By admin, in Databases | 1

This blog will teach you how to create dynamic query in mysql using stored procedure.

  • Dynamic SQL is a programming technique that enables us to write SQL statements dynamically at run time.
  • Dynamic Queries are not embedded in the source program but stored as strings that are manipulated during program’s run time.
  • Dynamic SQL statements may change from one execution to the next without manual intervention.

Make data easy with Helical Insight.
Helical Insight is world’s best open source business intelligence tool.

Get your 30 Days Trail Version

Below is the example of Stored Procedure that I have written:


CREATE PROCEDURE `GetFruits`(IN fruitArray VARCHAR(255))


DECLARE a varchar(65535);

DECLARE query1 varchar(65535);

DECLARE query2 varchar(65535);

DECLARE query3 varchar(65535);

DECLARE innerQuery varchar(65535);

set a = REPLACE(fruitArray,’\”,”);

set query1 = ‘select \’apple is selected\’ as name’;

set query2 = ‘select \’banana is selected\’ as name’;

set query3 = ‘select \’None of them are selected\’ as name’;

set innerQuery = ”;

IF(INSTR(a, ‘apple’)) THEN

set innerQuery = CONCAT(innerquery,query1);


IF(INSTR(a, ‘banana’)) THEN

IF(LENGTH(innerQuery)=0) THEN

set innerQuery = CONCAT(innerquery,query2);


set innerQuery = CONCAT(innerquery,’ UNION ‘,query2);



set @Query = CONCAT(‘select * from (‘,innerQuery,’) a’);

prepare b from @Query;

execute b;

END $$


a : – this variable will hold whatever input we will give at the time of calling the stored procedure

Make data easy with Helical Insight.
Helical Insight is world’s best open source business intelligence tool.

Click Here to Free Download

Below we can feel the output :

CALL GetFruits(‘apple’);apple

CALL GetFruits(‘banana’);




CALL GetFruits(“‘apple’,’banana'”);both

Rupam Bhardwaj

Helical IT Solutions


Best Open Source Business Intelligence Software Helical Insight is Here


A Business Intelligence Framework

0 0 votes
Article Rating
Notify of
1 Comment
Newest Most Voted
Inline Feedbacks
View all comments

Nice Article!
It really helps to all people of database community.
I have also prepared one small demo to write dynamic sql using prepared statement of MySQL.
You can visit my article here: