Getting "Error Code: 1525. Incorrect DATE value: ''" when creating a SP in MySQL

Question

I wrote this SP in MySQL version: 8.0.34 (MySQL Community Server - GPL) running in docker

CREATE DEFINER=`root`@`%` PROCEDURE `SearchEvents`(IN P_team_id INT, IN P_event_name VARCHAR(255), IN P_event_start_date DATE, IN P_event_end_date DATE)
BEGIN
    DECLARE mainQuery VARCHAR(1000);
    SET @mainQuery = 'SELECT * FROM events WHERE 1';

    IF P_team_id IS NOT NULL AND P_team_id <> '' THEN
        SET @mainQuery = CONCAT(mainQuery, ' AND team_id = ', P_team_id);
    END IF;

    IF P_event_name IS NOT NULL AND P_event_name <> '' THEN
        SET @mainQuery = CONCAT(mainQuery, ' AND (event_name_en LIKE "%', P_event_name, '%" OR event_name_ar LIKE "%', P_event_name, '%")');
    END IF;

    IF P_event_start_date IS NOT NULL AND P_event_start_date <> '' THEN
        SET @mainQuery = CONCAT(mainQuery, ' AND event_start_date >= \'', P_event_start_date,'\'');
    END IF;

    IF P_event_end_date IS NOT NULL AND P_event_end_date <> '' THEN
        SET @mainQuery = CONCAT(mainQuery, ' AND event_end_date <= \'', P_event_end_date,'\'');
    END IF;

    PREPARE stmt FROM @mainQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

and when it's executed call ssgg.SearchEvents(1, '', NULL, NULL); it should return all events related to team 1. However, the following error is returned Error Code: 1525. Incorrect DATE value: ''

I tried calling it using actual dates call ssgg.SearchEvents(1, '', '2023-01-01', '2023-12-31'); which should get last year's events but got the same result. Any advise?

UPDATE

as per suggestions I removed the comparison of P_event_start_date & P_event_end_date with ''.

Now a new error was returned

Error Code: 1064. 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 'NULL' at line 1.

I am calling the SP like this call ssgg.SearchEvents(1, '', NULL, NULL);

Answer 1

For a DATE type parameter like P_event_start_date and P_event_end_date, it doesn't make sense to check for an empty string ('') as it's not a valid representation for a date in MySQL. Hence, it's more appropriate to only check for NULL for date parameters.

CREATE PROCEDURE `SearchEvents`(IN P_team_id INT, IN P_event_name VARCHAR(255), IN P_event_start_date DATE, IN P_event_end_date DATE)
BEGIN
    DECLARE mainQuery VARCHAR(1000);
    SET @mainQuery = 'SELECT * FROM events WHERE 1';

    IF P_team_id IS NOT NULL AND P_team_id <> '' THEN
        SET @mainQuery = CONCAT(@mainQuery, ' AND team_id = ', P_team_id);
    END IF;

    IF P_event_name IS NOT NULL AND P_event_name <> '' THEN
        SET @mainQuery = CONCAT(@mainQuery, ' AND (event_name_en LIKE "%', P_event_name, '%" OR event_name_ar LIKE "%', P_event_name, '%")');
    END IF;

    IF P_event_start_date IS NOT NULL THEN
        SET @mainQuery = CONCAT(@mainQuery, ' AND event_start_date >= \'', P_event_start_date,'\'');
    END IF;

    IF P_event_end_date IS NOT NULL THEN
        SET @mainQuery = CONCAT(@mainQuery, ' AND event_end_date <= \'', P_event_end_date,'\'');
    END IF;

    PREPARE stmt FROM @mainQuery;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

See this db<>fiddle.