How to return boolean based on number of records in database?

user_5885266 0
Viewed 44

Here's what I've tried. My host is returning an error, "Sorry an unexpected error happened!" .

I want it to return true if there is at least 1 record with combination pdriver_id, ptruck_number, and pdate.

DELIMITER %%
CREATE FUNCTION DriverActiveInTruckByDate(
    pdriver_id INT,
    ptruck_number INT,
    pdate DATETIME
) 
RETURNS boolean
DETERMINISTIC
BEGIN
    DECLARE inDB INT DEFAULT 0;
    SET inDB = 
        SELECT IF(COUNT(*) >= 1,1,0)
    FROM
        truck_timeline tl
    WHERE 1=1
        AND tl.driver_id = pdriver_id
        AND tl.truck_number = ptruck_number
        AND ((pdate BETWEEN tl.begin_date AND tl.end_date) OR (pdate >= tl.begin_date AND tl.end_date IS NULL))
    
END
%%
DELIMITER ;
stored-functions mysql
2 Answers

Several fixes are needed:

  • The function is not DETERMINISTIC. This means the result will always be the same given the same inputs. In your case, the result may be different depending on the data in your truck_timeline table. So I would suggest using READS SQL DATA.

  • If you use SET variable = SELECT... you must put the SELECT in a subquery:

    SET inDB = (SELECT ...);

  • The current manual recommends using SELECT ... INTO variable instead of SET. See https://dev.mysql.com/doc/refman/8.0/en/select-into.html

    The INTO position at the end of the statement is supported as of MySQL 8.0.20, and is the preferred position.

    SELECT ... INTO inDB;

  • The function you show doesn't have a RETURN statement. See https://dev.mysql.com/doc/refman/8.0/en/return.html

    There must be at least one RETURN statement in a stored function.

Bill_Karwin_208600

Your Full Code could be like this:

DELIMITER %%
CREATE FUNCTION DriverActiveInTruckByDate(
    pdriver_id INT,
    ptruck_number INT,
    pdate DATETIME
) 
RETURNS boolean
DETERMINISTIC
BEGIN
    DECLARE inDB INT DEFAULT 0; 
        SET inDB = 
        (SELECT IF(COUNT(*) >= 1,1,0)
    FROM
        truck_timeline tl
    WHERE 1=1
        AND tl.driver_id = pdriver_id
        AND tl.truck_number = ptruck_number
        AND ((pdate BETWEEN tl.begin_date AND tl.end_date) OR (pdate >= tl.begin_date AND tl.end_date IS NULL))
        );
END %%
DELIMITER ;
user_194690880
Related