Create Dynamic Query In MYSQL Using Stored Procedure

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.

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

DELIMITER $$

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

BEGIN

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);

END IF;

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

IF(LENGTH(innerQuery)=0) THEN

set innerQuery = CONCAT(innerquery,query2);

ELSE

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

END IF;

END IF;

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

prepare b from @Query;

execute b;

END $$

DELIMITER ;

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

Below we can feel the output :

CALL GetFruits(‘apple’);

CALL GetFruits(‘banana’);

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

Rupam Bhardwaj

Helical IT Solutions

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download