Insert (where) if it is not in the query MySQL

Question

I've got a stored procedure in which I've 3 parameters. The situation is that the user will provide values for all parameters sometimes and sometimes for 2 parameters and sometimes for 1. How would I know that in which parameter's query I have to put WHERE clause? My stored procedure sample below:

    DELIMITER $$
    CREATE PROCEDURE getEmployeeDetails
    (
        IN ColumnName1 int(10)
        IN ColumnName2 int(10)
        IN ColumnName3 int(10)
    )
    BEGIN
        SET @SQLText = CONCAT('SELECT * FROM `table1`',
                       IF(ColumnName1 != '', CONCAT('WHERE ColumnName1 = ', ColumnName1), '',
                       IF(ColumnName2 != '', CONCAT('AND ColumnName2 = ', ColumnName2), '',
                       IF(ColumnName3 != '', CONCAT('AND ColumnName3 = ', ColumnName3), '');    
        PREPARE stmt FROM @SQLText;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END$$
    DELIMITER ;

Thanks in advance!

This is extraordinarily risky since you just SQL inject the values. Why do you need a stored procedure for this? Composing it in your application code would be significantly safer. — tadman
– tadman, Commented Oct 21, 2022 at 10:53

flyingfox · Accepted Answer · 2022-10-21 10:52:17Z

1

The key point is that you can add WHERE 1=1 in your original sql,and then you can add the query condition dynamiclly.

SET @sqlText = 'SELECT * FROM `table1` WHERE 1=1 ';
IF(ColumnName1 != '')
  SET @sqlText = CONCAT(@sqlText,' AND ColumnName1 = ',ColumnName1)
END IF;
IF(ColumnName2 != '')
  SET @sqlText = CONCAT(@sqlText,' AND ColumnName2 = ',ColumnName2)
END IF;
IF(ColumnName3 != '')
  SET @sqlText = CONCAT(@sqlText,' AND ColumnName3 = ',ColumnName3)
END IF;

edited Oct 21, 2022 at 10:52

answered Oct 21, 2022 at 10:44

flyingfox

13.5k4 gold badges30 silver badges43 bronze badges

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

1 Comment

Abdul Raheem Dumrai Over a year ago

Thanks a lot for your fastest reply Buddy!

Collectives™ on Stack Overflow

Insert (where) if it is not in the query MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related