CODEWITHSUNDEEP
mysqlsql
Ajinkya
Ajinkya

Reputation: 1711

Dynamic query where clause check

This is my MySQL procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `awaredb2`.`GetAlertList`$$

CREATE DEFINER=`aware`@`%` PROCEDURE `GetAlertList`(IN deviceIds VARCHAR(200), IN appIds VARCHAR(30000))
BEGIN


        SET @QUERY = '  SELECT P.policy_id AS PolicyId,
                        P.name AS PolicyName,
                        (MAX(A.alert_create_date)) AS AlertCreateDate, ';

        IF deviceIds != ''
        THEN
            SET @QUERY = CONCAT(@QUERY, '   SUM(AR.penalty) AS Penalty, ');
        ELSE
            SET @QUERY = CONCAT(@QUERY, '   '''' AS Penalty, ');
        END IF;

        SET @QUERY = CONCAT(@QUERY,'    A.device_id AS DeviceId
                    FROM alert A
                        JOIN alert_rule AR ON AR.alert_id = A.id
                        JOIN policy P ON P.id = A.policy_id 
                        JOIN device D ON D.id = A.device_id
                    WHERE P.name != '''' ');

        IF deviceIds != ''
        THEN
            SET @QUERY = CONCAT(@QUERY, ' AND A.device_id IN(', deviceIds, ') ');
        END IF;
        -- 
        IF appIds != ''
        THEN
            SET @QUERY = CONCAT(@QUERY, ' AND A.app_id IN(', appIds, ') ');
        END IF;


        SET @QUERY = CONCAT(@QUERY, '   GROUP BY P.name
                        ORDER BY AlertCreateDate DESC ');
        PREPARE stmt FROM @QUERY;
        EXECUTE stmt;
        SELECT @QUERY;
    END$$

DELIMITER ;

This procedures accepts two parameters. If parameter value is not empty, that value would be used in order to prepare sql statement and execute in the end.

Now I am asked to remove first default condition P.name != ''. If I remove this condition every time I will have to check other two parameters are present or not. If present then add where clause. If both are present then add AND before second check else add it after where clause.

Later there could be more than two parameters for this procedure. So what is the best way to manage this dynamic query with WHERE and AND?

Upvotes: 2

Views: 500

Answers (1)

lc.
lc.

Reputation: 116448

The simplest solution is just to keep a WHERE 1=1 in your query. The optimizer will get rid of it and all you have to do is add AND ... blocks.

See also http://dev.mysql.com/doc/internals/en/optimizer-primary-optimizations.html , section 8.2.1.2:

A transformation takes place for conditions that are always true, for example:

WHERE 0=0 AND column1='y'

In this case, the first condition is removed, leaving

WHERE column1='y'

Upvotes: 1

Related Questions