Hi

I have a stored procedure with a cursor which check if a field is filled in after a certain deadline.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `controleDeadlines`()
BEGIN

    #declaratie variabelen die nodig zijn
    declare cId int;
    declare cDeadline text;
    declare cUitgevoerd date;
    declare cOpmerking text;
    declare cStatus text;
    declare DONE int default FALSE;
    declare tempdate date;

    #declaratie cursor
    declare cursorDeadlinesIndicatoren cursor for
    select id,deadline,status,opmerking,uitgevoerd from deadlines_indicatoren d
    join indicatoren i on d.ind_id = i.IND_Id
    where i.Type_Uitgevoerd="datum";
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;

    #cursor starten
    OPEN cursorDeadlinesIndicatoren;
        read_loop: loop
            fetch cursorDeadlinesIndicatoren into cId,cDeadline,cStatus,cOpmerking,cUitgevoerd;

            IF DONE THEN
                leave read_loop;
            END IF;

            #omzetten deadline naar geldig date type
            IF ((cUitgevoerd = '' or cUitgevoerd is null) and ( str_to_date(cDeadline,'%d/%m/%Y')< current_date)) then
                UPDATE deadlines_indicatoren
                set deadlines_indicatoren.uitgevoerd ="01/01/3000",deadlines_indicatoren.opmerking="vergeten invullen"
                where deadlines_indicatoren.id = cId;
            END IF;         
        END LOOP;
    close cursorDeadlinesIndicatoren;   
END

It works perfect except in one case: when the "cUitgevoerd" var is empty and not null then cUitgevoerd='' doesn't seems to work. Is there a mistake in my if clause?

Thanks in advance

You need to check for null value before empty... Swap the condition and it should be OK.

I solved it,
cUitgevoerd should be text because i use a textfield for uitgevoerd (uitgevoerd can be a date or an int).

Swapping the condition doesn't do anything but thanks for the suggestion Taywin.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.