CODEWITHSUNDEEP
mysqlsqlif-statementstored-procedures
David Ackerman
David Ackerman

Reputation: 53

mysql procedure with if condition

I'm in my first databases class and I'm trying to write a conditional block for a mysql procedure.

This is the procedure:

delimiter //
CREATE PROCEDURE add_ascent(IN cid INT, IN pid INT)
BEGIN
    DECLARE count_ascents INT;
    SET count_ascents = 0;
    SELECT COUNT(`cid`) INTO count_ascents FROM ascents WHERE `cid`=cid AND `pid`=pid;
    IF count_ascents < 1 THEN
        INSERT INTO ascents (`cid`, `pid`) VALUES (cid, pid);
        UPDATE climbers SET climbers.ascents = climbers.ascents + 1 WHERE climbers.id=cid;
        UPDATE problems SET problems.ascents = problems.ascents + 1 WHERE problems.id=pid;
    END IF;
END;
//
delimiter ;

The goal of the procedure is to only perform the insert and updates if the (cid, pid) pair is not in the the ascents database. After testing, the program doesn't seem to go into the if block at all.

Upvotes: 2

Views: 53

Answers (1)

paulsm4
paulsm4

Reputation: 121669

FYI, you might want to consider using an UPSERT, instead of "select/if/insert". For example, mySQL offers INSERT ON DUPLICATE KEY UPDATE.

Here, I suggest:

  1. giving your parameters a DIFFERENT name than the column name, for example iCid and iPid, then

  2. Typing SELECT COUNT(cid) INTO count_ascents FROM ascents WHERE cid=iCid AND pid=iPid and checking the result.

Upvotes: 1

Related Questions