0

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

2
Contributors
2
Replies
12
Views
2 Years
Discussion Span
Last Post by nevek
0

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

0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.