how to generate dynamic where condition in mysql procedure

Question

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 ;

Sagar Gangwal · Accepted Answer · 2017-02-22 06:23:11Z

3

 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.

edited Feb 22, 2017 at 6:23

answered Feb 22, 2017 at 5:40

Sagar Gangwal

8,1303 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Juned Ansari Over a year ago

it shows an error like "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WhereCondition = "1=1"; IF NULLIF(PID, '') IS NULL WhereCo' at line 12"

Juned Ansari Over a year ago

same error "Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WhereCondition = "1=1"; ELSEIF NULLIF(PID, '') IS NULL Whe' at line 12"

Collectives™ on Stack Overflow

how to generate dynamic where condition in mysql procedure

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related