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:
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
set innerQuery = CONCAT(innerquery,query2);
set innerQuery = CONCAT(innerquery,’ UNION ‘,query2);
set @Query = CONCAT(‘select * from (‘,innerQuery,’) a’);
prepare b from @Query;
a : – this variable will hold whatever input we will give at the time of calling the stored procedure
Below we can feel the output :
Helical IT Solutions