MySQL Stored Procedure Programming

Name

DAT-02: Avoid overriding variable declarations within "inner" blocks

Synopsis

It is possible to declare a variable inside an inner block that has the same name as a variable in the enclosing block. Nevertheless—though legal—this practice can be extremely confusing.

For instance, in the following example the v_counter variable is declared both within the inner block and within the outer block:

    DECLARE  v_counter INT DEFAULT 1;

      . . . Lots of code . . .

      inr_blk: BEGIN
        DECLARE v_counter INT DEFAULT 2;

        . . . Lots of code . . .

        SET v_counter=v_counter+1;

      END inr_blk;

There are two undesirable consequences to this practice:

• Someone reading the code might be confused as to which variable is being updated.

• It is not possible in the inner block to modify the value of a variable in the outer block. The SQL:2003 specification allows us to prefix a variable name with its block label, but this isn't supported in MySQL yet.

It's much better to ensure that every variable declared in a stored program has a unique name, regardless of its block scope.