CODEWITHSUNDEEP
mysqlstored-procedures
user1868626
user1868626

Reputation: 73

MySQL stored procedure error: unknown system variable

I have a problem with a stored procedure in MySQL 5.5. This is the table on which the procedure must work:

Table `diba`:
----------------------------------------------------
Column   | Type    | Null | Default | Links to
----------------------------------------------------
ParentID | int(11) | No   |         | articoli -> id         
ChildID  | int(11) | No   |         | articoli -> id
Quantity | int(11) | No   |
lvl      | int(11) | No   | 0

and this is the procedure itself:

DELIMITER //
CREATE PROCEDURE RenumberLevels()
DETERMINISTIC
BEGIN
DECLARE lvl_counter INT;
SET lvl_counter = 1;
UPDATE `diba` SET `diba`.`lvl` = CASE WHEN `diba`.`ParentID` IS NULL THEN 1 ELSE 0 END;
WHILE EXISTS (SELECT * FROM `diba` WHERE `diba`.`lvl` = 0) DO
UPDATE `diba` SET `diba`.`lvl` = lvl_counter +1 WHERE (SELECT `D2`.`lvl` FROM `diba` AS D2
WHERE D2.ChildID = `diba`.`ParentID`) > 0 AND `diba`.`lvl` = 0;
SET `diba`.`lvl` = lvl_counter + 1;
END WHILE;
END//
DELIMITER ;

The resulting error is: #1193 - Unknown system variable 'lvl'

What's wrong with this procedure?

Upvotes: 4

Views: 9860

Answers (2)

user1868626
user1868626

Reputation: 73

This is the correct version of the create procedure statement:

DELIMITER $$
CREATE PROCEDURE `RenumberLevels`()
DETERMINISTIC
BEGIN
  DECLARE lvl_counter INT;
  SET lvl_counter = 1;
  UPDATE `diba` SET `diba`.`lvl` = CASE WHEN `diba`.`ParentID` IS NULL THEN 1 ELSE 0 END;
  WHILE EXISTS (SELECT * FROM diba WHERE lvl = 0) DO
    CREATE TABLE dibatemp AS
      SELECT D2.lvl FROM diba AS D2 LEFT JOIN diba ON diba.ParentID=D2.ChildID;
    UPDATE diba
      SET lvl = lvl_counter +1 WHERE lvl IN (SELECT lvl FROM dibatemp) > 0 AND lvl = 0;
    DROP TABLE dibatemp;
    SET lvl_counter = lvl_counter + 1;
  END WHILE;
END$$
DELIMITER ;

Upvotes: 3

Joni
Joni

Reputation: 111259

On the line that says

SET `diba`.`lvl` = lvl_counter + 1;

I think you meant 

SET lvl_counter = lvl_counter + 1;

Upvotes: 1

Related Questions