Inaccurate results in stored procedure

Yukke · #1 (**permalink**) 10-22-07, 16:13

I have a very strange problem regarding a section of my stored procedure giving inaccurate results.

The section of the code in question is:

Code:

 case reprint_type
  when 'J'
    then
begin
select a.modified_by
into user_id
from adfapp.job_history a
where a.job_id = job_id
and a.state_name = 'Reprint'
and a.time_stamp = (select max(b.time_stamp) from adfapp.job_history b where b.job_id = job_id and b.state_name = 'Reprint')
with ur;
end;

For example, when this stored procedure is ran with a job_id of 513145, the value user_id is 'jackson'... which I know is the incorrect user id.

However, when I just run this simple select statement, I get the correct user id:

Code:

select a.modified_by
from adfapp.job_history a
where a.job_id = 513145
and a.state_name = 'Reprint'
and a.time_stamp = (select max(b.time_stamp) from adfapp.job_history b where b.job_id = 513145 and b.state_name = 'Reprint')

What could be the cause of this?
The entire stored proc is attached for reference...

Thanks for your help!

Yukke · #2 (**permalink**) 10-23-07, 09:57

I seem to have fixed the problem but I am not sure why...

all I did was change the variable job_id to a different name, e.g. jobid

Code:

 
declare jobid integer;     
case reprint_type
  when 'J'
    then
begin
select a.modified_by
into user_id
from adfapp.job_history a
where a.job_id = jobid
and a.state_name = 'Reprint'
and a.time_stamp = (select max(b.time_stamp) from adfapp.job_history b where b.job_id = jobid and b.state_name = 'Reprint')
with ur;
end;

Can someone maybe explain why this happens?
Does DB2 get confused when a column name and variable have the same name?

jsharon1248 · #3 (**permalink**) 10-23-07, 10:17

It's all about scope. DB2 resolved the reference of job_id within the context of your SELECT statement. In other words, your predicate was comparing the value of column a.job_id to the value of column job_id, which in your statement will always result in true. You changed the variable name to jobid which is not a column on the table, and now DB2 resolves the reference to your stored proc variable.

jsharon1248 · #4 (**permalink**) 10-23-07, 10:32

One more thought. You should be able to reference the original variable name and distiquish it from the table column name if you label your BEGIN and qualify the reference. I think it's better to use different variable names, but this might be something you'd want to try. For clarity, you could also prefix the stored proc variables and parms with v and p (vJob_Id, pParm1)

Code:

lab1: BEGIN
declare job_id integer;

select a.modified_by
into user_id
from adfapp.job_history a
where a.job_id = lab1.job_id

Marcus_A · #5 (**permalink**) 10-23-07, 22:38

This sounds like a maintenance nightmare. The fact that the OP decided to try changing the name of the variable suggests it should in fact have a different name, not only to make the code work properly, but to make it understandable and maintainable by others.

Internet Services

Web Development

Digital Marketing

Consumer Tech