Building Dynamic Query in Mysql Stored Procedure

Posted on by | 1 Reply

Table Structure

1
2
3
4
5
6
7
8
9
CREATE TABLE Areas(AreaName VARCHAR(255),
                    PinCode VARCHAR(255))
                     
INSERT INTO Areas(AreaName, PinCode)VALUES
                 ('Teynampet', '6000018'),
                 ('Ramapuram', '6000089'),
                 ('TNagar', '6000017'),
                 ('Mylapore', '6000014'),
                 ('Gopalapuram', '6000087')

Stored Procedure

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
DROP PROCEDURE IF EXISTS mp_test;
CREATE PROCEDURE mp_test(IN pArea VARCHAR(255))
   BEGIN
      SET @Query = '';
      SET @City  = '';
      SET @Query = 'SELECT PinCode FROM Areas';
 
      IF pArea != '' THEN
         SET @City = CONCAT(' WHERE AreaName = "', pArea, '"');
      END IF;
       
      SET @Query = CONCAT(@Query, @City);
 
 PREPARE stmt FROM @Query;
  
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt; 
END

One thought on “Building Dynamic Query in Mysql Stored Procedure”

Leave a reply