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

Recommended Answers

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

Jump to Post

All 2 Replies

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 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.