Stored Procedures variable assignments on Cursor

Question

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 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);

        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"

Answer 1

Your test query obfuscates whatever you are actually doing so much that it's really impossible to pinpoint the underlying problem. You have a CONTINUE HANDLER setting a variable ("done") that is not only not tested (to break out of a loop that also doesn't exist), it isn't even defined, so the code as written is too vague (and, the community requires code examples to be valid -- producing the wrong result doesn't make it invalid by definition, but not being runnable does, and this code should not work with an undeclared variable).

Presumably you know that var1 is a program variable and @var1 is a user-defined variable, meaning that the two are completely unrelated. I've never seen program variables used in queries with the := assignment hackery, so it could be that this isn't valid (and would make sense, if it isn't, because the two types of variables can behave very differently).

If the problem is that simple, then:

SET @var1 = var1;

...at an apprioriately-early part of the proc, and use the user-defined variable @var1 in the query.

While Iit seems like a strange thing to require, it's not the only case where something like this (copying a program variable into a user-defined variable) might be needed... prepared statements can only use user-defined variables, too.