CODEWITHSUNDEEP
mysqlsqlstored-procedures
jmasterx
jmasterx

Reputation: 54123

Date is not getting set

I have a procedure that should always set a date, but it does not:

CREATE PROCEDURE 'player_extend_membership` (pid INTEGER, daysToAdd INTEGER, OUT result INTEGER)
BEGIN
SELECT PlayerMembershipEndDate INTO @memDate FROM players WHERE players.PlayerID = pid LIMIT 1;
SELECT ROW_COUNT() INTO @num;

IF @num = 0 THEN
    SET result = -1;
ELSE

    IF @memDate = NULL OR DATE(@memDate) < DATE(NOW()) THEN
        SET @finalDate = DATE_ADD(DATE(NOW()), INTERVAL daysToAdd DAY);
    ELSE
        SET @finalDate = DATE_ADD(DATE(@memDate), INTERVAL daysToAdd DAY);
    END IF;

SELECT @finalDate, @memDate;
UPDATE players SET PlayerMembershipEndDate = @finalDate
WHERE players.PlayerID = pid;
SET result = 1;
END IF;
END

When I check the return value, it is 1, therefore the account does exist. It tells me the result of the select query is always that @finalDate is NULL.

However, if ake it out of the IF and just do:

SET @finalDate = DATE_ADD(DATE(NOW()), INTERVAL daysToAdd DAY);

The date is set correctly.

I'm not sire what I am doing wrong.

Thanks

Upvotes: 0

Views: 23

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270001

Your procedure seems way too complicated. Perhaps this does what you want:

set @result = -1;
UPDATE players
    SET PlayerMembershipEndDate = (case when (@result := 1) is null then NULL
                                        when @memDate IS NULL OR DATE(@memDate) < DATE(NOW())
                                        then DATE_ADD(DATE(NOW()), INTERVAL daysToAdd DAY)
                                        else DATE_ADD(DATE(@memDate), INTERVAL daysToAdd DAY)
                                   end)
    WHERE players.PlayerID = pid;

The first condition in the case just sets @result if a row is found. I've left your formulation of DATE(NOW()) even though CURDATE() is more succinct.

I suspect the actual problem with your logic was the = NULL. This always returns "UNKNOWN", which is treated as false. The correct expression is is NULL.

Upvotes: 1

Related Questions