Mysql - How to use user defined variables on pointers

Question

I´m new to InnoDB and starting with transactions. I´ve been 24 hours trying to get this to work. I´m creating an exchange site and really need a transaction to be made. First, make a Select and find some data, and then some updates and inserts according with the results given.

I won´t post the full query as it might be very complicated to read so I created a new query to point out whats bothering.

Table Log

    CREATE TABLE `log` (
      `num_rows` int(10) unsigned NOT NULL,
      `new_value` int(10) unsigned NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Stored Procedure

    DROP PROCEDURE IF EXISTS `test`//

    CREATE PROCEDURE `test` (IN var1 BIGINT) 
    BEGIN
      DECLARE done INT DEFAULT 0;
      DECLARE result INT;
      DECLARE num_rows INT;

      DECLARE cur1 CURSOR FOR
        SELECT @var1 := @var1 +1 AS result;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

      START TRANSACTION;
        OPEN cur1;
        SELECT FOUND_ROWS() into num_rows;
        INSERT INTO log (num_rows,new_value) VALUES (num_rows,var1);

        read_loop:
        LOOP
          FETCH cur1 INTO result;
          IF done = 1 THEN
              LEAVE read_loop;
          END IF;
        END LOOP read_loop;

        CLOSE cur1;
      COMMIT;
    END//

When I try

    CALL test(1);

Im passing 1 as var1 parameter. So in cur1, the value should be increased. And later insert a new row to the log with the new value. It looks like := asignment isn´t working.

I actually changed

    SELECT @var1 := @var1 +1 AS result;

for this

    SELECT var1 := var1 +1 AS result;

And get an error on ":= var1 +1"

See [MySQL: @variable vs. variable. Whats the difference?](http://stackoverflow.com/a/1010042) — eggyal, Dec 27 '13 at 15:29

score 0 · Accepted Answer · answered Dec 28 '13 at 09:47

I understand that the code of the question is an abstraction of the actual code of the stored procedure, so do not quite understand what you need to do, however, a code like this can be helpful.

/* Procedure structure for procedure `test` */

/*!50003 DROP PROCEDURE IF EXISTS  `test` */;

DELIMITER $$

CREATE PROCEDURE `test`(IN `var1` BIGINT)
BEGIN
    DECLARE `done` TINYINT(1) DEFAULT 0;
    DECLARE `result` BIGINT;
    DECLARE `_num_rows` INT;
    DECLARE `cur1` CURSOR FOR
        SELECT SQL_CALC_FOUND_ROWS @`var1` := `var1` + 1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done` := 1;
    START TRANSACTION;
    OPEN `cur1`;
    SET `var1` := @`var1`;
    SELECT FOUND_ROWS() INTO `_num_rows`;
    INSERT INTO `log` (`num_rows`, `new_value`) VALUES (`_num_rows`, `var1`);
    `read_loop`: LOOP
      FETCH `cur1` INTO `result`;
      IF (`done`) THEN
          LEAVE `read_loop`;
      END IF;
    END LOOP `read_loop`;
    CLOSE `cur1`;
    COMMIT;
END$$

DELIMITER ;

It is important to indicate the difference between 9.4. User-Defined Variables and routine parameters 13.1.15. CREATE PROCEDURE and CREATE FUNCTION Syntax, are different variables.

SQL Fiddle demo

Mysql - How to use user defined variables on pointers

1 Answers1