Dynamic query in stored procedure with a Date parameter in MySQL

Question

I am writing a stored procedure in MySQL with a dynamic query as shown below:

DROP PROCEDURE IF EXISTS test;
DELIMITER //
CREATE PROCEDURE test(
IN STATUS VARCHAR(20),
IN CREATED_ON1 VARCHAR(10)
) 
BEGIN

SET @query="SELECT STATUS, COUNT(*) FROM CONTROL WHERE ID=ID";

IF STATUS IS NOT NULL THEN
 SET @query=CONCAT(@query, " AND STATUS='",STATUS,"'");
END IF;

IF CREATED_ON1 IS NOT NULL THEN
 SET @query=CONCAT(@query, " AND DATE_FORMAT(CREATED_ON,'%d-%m-%Y')>='", 
CREATED_ON1, "'");
END IF;

SET @query=CONCAT(@query, " GROUP BY STATUS");

PREPARE stmt FROM @query;
EXECUTE stmt; 
DEALLOCATE PREPARE stmt;

END //
DELIMITER ;

I intend to rewrite this procedure with the signature shown below. This will provide the right logic in comparing the dates.

DROP PROCEDURE IF EXISTS test;
DELIMITER //
CREATE PROCEDURE test(
IN STATUS VARCHAR(20),
IN CREATED_ON1 DATE
)

The main question is; how do I concatenate the predicate with date to the @query?

Jacques Amar · Answer 1 · 2017-10-30T03:15:25.157

Try using a CASE and pass the parameters:

DROP PROCEDURE IF EXISTS test;
DELIMITER //
CREATE PROCEDURE test(
IN IN_STATUS VARCHAR(20),
IN IN_CREATED_ON1 DATE
)

BEGIN
SET @v_and    = ' AND ';
SET @v_where  = ' WHERE ';
SET @v_query  = 'SELECT STATUS, COUNT(*) FROM CONTROL';
SET @v_status = ' STATUS=? ';
SET @v_date   = ' CREATED_ON >= ? ';
SET @v_group  = ' GROUP BY STATUS';

CASE 

WHEN IN_STATUS IS NOT NULL && IN_CREATED_ON1 IS NOT NULL THEN
    SET @v_query = CONCAT(@v_query,@v_where,@v_status,@v_and,@v_date,@v_group);
    PREPARE stmt FROM @v_query;
    EXECUTE stmt USING @IN_STATUS, @IN_CREATED_ON1; 
WHEN IN_STATUS IS NOT NULL THEN
    SET @v_query = CONCAT(@v_query,@v_where,@v_status,@v_group);
    PREPARE stmt FROM @v_query;
    EXECUTE stmt USING @IN_STATUS; 
WHEN IN_CREATED_ON1 IS NOT NULL THEN
    SET @v_query = CONCAT(@v_query,@v_where,@v_date,@v_group);
    PREPARE stmt FROM @v_query;
    EXECUTE stmt USING @IN_CREATED_ON1; 
ELSE
    SET @v_query = CONCAT (@v_query,@v_group);
    PREPARE stmt FROM @v_query;
    EXECUTE stmt; 
END CASE;

DEALLOCATE PREPARE stmt;
END//

DELIMITER ;

Sorry this does not run well when I tested. ` call test("REALISED",STR_TO_DATE("2017-10-01", "%Y-%m-%d")); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHEN STATUS=? AND CREATED_ON >= ? GROUP BY STATUS' at line 1` — cdaiga, Oct 29 '17 at 22:18

score 1 · Accepted Answer · answered Oct 29 '17 at 22:08

1

Note that you do not have to use dynamic SQL. Using normal stored procedure would make your code much more readable and easier to maintain:

CREATE PROCEDURE test(
IN_STATUS VARCHAR(20),
IN_CREATED_ON DATE
) 
BEGIN

SELECT STATUS, COUNT(*) 
FROM CONTROL 
WHERE STATUS=IFNULL(IN_STATUS, STATUS) AND CREATED_ON>=IFNULL(IN_CREATED_ON,CREATED_ON)
GROUP BY STATUS;

END

Assuming your STATUS and CREATED_ON would always contain value.

answered Oct 29 '17 at 22:08

slaakso

8,331
2
16
27

You are implicitly building the query using the `IFNULL` function. It runs well and and is concise. – cdaiga Oct 29 '17 at 22:15
@slaakso - very neat trick - always love to learn new ones. Question: Do you know if the optimizer will do away with the redundant `WHERE` parts when `NULL` is passed? – Jacques Amar Oct 30 '17 at 17:29
Not likely. The use of function with the comparision operation may affect optimizers index choosing decision even if the variable would contain value. – slaakso Oct 30 '17 at 19:17
For large data and for optimizer, a better WHERE-clause would probably be "WHERE (IN_STATUS is null or (IN_STATUS is not null and STATUS = IN_STATUS)) AND (IN_CREATED_ON is null or (IN_CREATED_ON is not null and CREATED_ON=IN_CREATED_ON))". MySQL optimizer has room for improvement. – slaakso Oct 30 '17 at 19:26

Dynamic query in stored procedure with a Date parameter in MySQL

2 Answers2