Q:

how to generate dynamic where condition in mysql procedure

I have created one Store procedure in mysql it accept 2 parameter but parameter can be null so based on that parameter value i wanted to generate dynamic where condition 
DELIMITER$$
CREATE PROCEDURE GetValue(IN PID INT,IN PName VARCHAR(255))
 BEGIN
 SELECT * 
 FROM tblTEMP
 WHERE ID= PID AND NAME=;  
 END$$
DELIMITER ;

but in above Store procedure problem is PID or PName either one or both may Null in that case what should i write in where condition?
i tried like this 
DELIMITER$$
    CREATE PROCEDURE GetValue(IN PID INT,IN PName VARCHAR(255))
     BEGIN
        DECLARE WhereCondition VARCHAR(300);
         IF NULLIF(PID, '') IS NULL AND NULLIF(PName , '') IS NULL
             WhereCondition = "1=1";
         IF NULLIF(PID, '') IS NULL
             WhereCondition = "NAME=PName";  
         IF NULLIF(PName, '') IS NULL
             WhereCondition = "ID=PID";
     SELECT * 
     FROM tblTEMP
     WHERE  WhereCondition ;  
     END$$
    DELIMITER ;

A:

 DELIMITER $$
    CREATE PROCEDURE GetValue(IN PID INT,IN PName VARCHAR(255))
     BEGIN
        DECLARE WhereCondition VARCHAR(300);

        IF NULLIF(PID, '') IS NULL AND NULLIF(PName , '') IS NULL THEN
           SET WhereCondition = '1=1';
        ELSEIF NULLIF(PID, '') IS NULL THEN
           SET WhereCondition = 'NAME=PName';  
        ELSEIF NULLIF(PName, '') IS NULL THEN
           SET WhereCondition = 'ID=PID';
        END IF;

         SET @query =CONCAT("SELECT * FROM tblTEMP ",WhereCondition);
         PREPARE stmt FROM @query;
         EXECUTE stmt;
     END$$
 DELIMITER ;

Just try above code.I had mention dynamic query using PREPARE statement and Execute it with Execute command.Hope this will help.

