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

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!

CodePudding user response：

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;