Scope of variables

The declaration of variables is local to the compound statement. If you’re using multiple compound statements in your SQL PL stored procedure you can declare variables with the same name in the nested compound statement. To reference to variables outside of this compound statement you can use a label.

Furthermore names will be verified first against column names then to variable names. To beware of a disarrangement it’s recommended to use a prefix for all variable names (e.g. ‚v_‘)
Example:

create procedure myproc (out var01 integer, out var02 integer, out var03 integer, out var04 integer, out var05 integer)
language sql

label1:begin

  declare myvar_01 integer;
  declare myvar_02 integer;
  declare myvar_03 integer;

  set myvar_01 = 1;
  set myvar_02 = 2;
  set myvar_03 = 3;

  begin

    declare myvar_01 integer;
    declare myvar_02 integer;

    set myvar_01 = 99;
    set myvar_02 = label1.myvar_02;

    set var03 = myvar_01;
    set var04 = myvar_02;
    set var05 = myvar_03;

  end;

  set var01 = myvar_01;
  set var02 = myvar_02;

end!

Output:

db2 "call myproc(?,?,?,?,?)"

 Value of output parameters
 --------------------------
 Parameter Name  : VAR01
 Parameter Value : 1

 Parameter Name  : VAR02
 Parameter Value : 2

 Parameter Name  : VAR03
 Parameter Value : 99

 Parameter Name  : VAR04
 Parameter Value : 2

 Parameter Name  : VAR05
 Parameter Value : 3

 Return Status = 0