Hi Friends,

I am using Db2v8.1.
Will DB2 allow me to declare a variable name same as the column name? I tried it. It gets successfully created. Here it is:
------------------------------------------------------------------
CREATE PROCEDURE SIMPLEFOR(OUT dept SMALLINT)
LANGUAGE SQL
BEGIN
FOR v2 AS
SELECT dept FROM staff
DO
SET dept = dept + dept;
END FOR ;
END
@
------------------------------------------------------------------

If I used the column name inside the for loop, how will DB2 differentiate between the column name and the variable name?
I expect some error messages to avoid these kind of conflicts. Please let me know if i am not correct.

Thanks & Regards,
Jake

Jake,
I believe that it is a scope issue. The "dept" inside the FOR loop is not the same ase the "dept" parameter. The columns defined in the select of the FOR loop are only valid within the FOR loop.

HTH

Andy

Hi Andy,

Thanks for your help. You are correct. It's the scoping which comes into play if there is ambiguity.

If the parameter name is accidently same as the column name, then it will generate unexcepted result.
So the lesson is
"Never use the parameter name that is same as the column name."

Once again thanks for solving this.

With Regards,
Jake

While it's not a good practice to have identical names and parameters, you can resolve your problem via params/fields qualifiers

CREATE PROCEDURE SIMPLEFOR(OUT dept SMALLINT)
LANGUAGE SQL
BEGIN
FOR v2 AS
SELECT dept FROM staff
DO
SET SIMPLEFOR.dept = SIMPLEFOR.dept + v2.dept;
END FOR ;
END
@


If the proc above not just an example, you can re-write it to
select sum(staff.dept) into SIMPLEFOR.dept from staff

Hi Chuzhoi,

Thanks for spending your valuable time to answer the question. It works fine after I qualified the names, as you mentioned. I wrote this procedure for learning the behaviour of the DB2. Anyway, thanks for the query.

With Regards,
Jake