CODEWITHSUNDEEP
mysqlsqlstored-procedurescursor
Jacob
Jacob

Reputation: 579

SQL cursor & stored procedure order arrangement

I was working on a stored procedure to update the order field in a product table. It works only the problem now is the the last item in the loop(cur), is increased twice instead of once (so dubbeled). Like so:

+-----------------+
|product + order  |
|_id     |        |
|        |        |
|  1     |   0    |
|  2     |   1    |
|  etc.. |   etc..|
|  36    |   35   |
|  37    |   36   |
|  38    |   38   |
|                 |
+-----------------+

I cant figure out why. The link table(CategoryProduct) in this case goes to 38 with a category_id of 2 CALL curorder(2);

Stored procedure:

DELIMITER //
CREATE PROCEDURE curorder(
    IN catid INT
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE i INT DEFAULT 0;
    DECLARE p INT;
    DECLARE cur CURSOR FOR SELECT product_id FROM test.CategoryProduct WHERE category_id = catid;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
        read_loop: LOOP
            FETCH cur INTO p;
            UPDATE  `test`.`Product` SET  `order` =  i WHERE  `Product`.`product_id` =p;
            SET i = i + 1;
            IF done THEN
                LEAVE read_loop;
            END IF;
        END LOOP;
    CLOSE cur;
END //
DELIMITER ;

The Database is a Mysql Database. Any suggestions for improving the procedure are always welcome.

Thanks in advance.

EDIT:

I already tried to place the SET i STATEMENT beneath the IF STATEMENT but with no result.

Upvotes: 1

Views: 376

Answers (1)

Teun Ouwehand
Teun Ouwehand

Reputation: 89

You should put:

IF done THEN
  LEAVE read_loop;
END IF;

Above your update statement, the last time mysql walks trough the loop is uses the old variables because there is no new 'p'. but i is incremented.

I good way to debug stored procedures is with a log table:

CREATE TABLE procedureLog
(
  id INTEGER AUTO_INCREMENT,
  description TEXT,
  PRIMARY KEY (id)
);

For this case you can log the update parameters with the follow query:

INSERT INTO  `test`.`procedureLog` (`id` ,`description`) VALUES (null,  CONCAT('id: ', CAST(p as CHAR), ' order: ', CAST(i as CHAR)));

Good luck!

Upvotes: 1

Related Questions