Creating Dynamic Query in Stored Procedure MySQL

Question

I have a Table and Rows in table like below

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

I Wrote a SQL Procedure as Below

DROP PROCEDURE IF EXISTS mp_test;
CREATE PROCEDURE mp_test(IN pArea VARCHAR(255))
   BEGIN
      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;
END 

When I Call the Procedure

CALL mp_test('Teynampet');

When i Execute i am Not getting the Desired result i.e is 600018

How can i build query dynamically in SP

Thanks for the Help

Answer 1

You're concatenating the pArea parameter into the SQL unquoted. That is, the content of @Query that you prepare for execution is:

SELECT PinCode FROM Areas WHERE AreaName = Teynampet

Since Teynampet is unquoted, it is parsed as a(n unknown) SQL identifier rather than a string. You should either:

• quote it in your SQL:

  SET @City  = CONCAT(' WHERE AreaName = ', QUOTE(pArea));
  

• pass it to the prepared statement as a parameter:

  SET @City  = CONCAT(' WHERE AreaName = ?');
  SET @param = pArea;
  

  and then:

  EXECUTE stmt USING @param;
  

However, why use prepared statements here? Your procedure can be rewritten as a simple SELECT (which raises the question of whether you need to use a stored procedure at all):

CREATE PROCEDURE mp_test(IN pArea VARCHAR(255))
  SELECT PinCode FROM Areas WHERE pArea IN (AreaName, '');

(Note that I'd recommend you use NULL instead of the empty string '', in which case the above test would be pArea IS NULL OR pArea = AreaName).