AS/400 stored procedure passing parameter
I have created a stored procedure with an input parameter of type varchar(100).
I am using the input variable in the query below
select deptid,name , max(location)
from
emp
where
empid in (empnos);
In which the empnos is the parameter i pass to the procedure and the variable is of type varchar. Empid in the query is an interger.
If i call the procedure with values ('6762,6764') it is doing rounding of empnos as 6763 and applying a where on empid = 6763 whereaas requirement is to do where as below
select deptid,name , max(location)
from
emp
where
empid in (6762,6764);
when i pass ('6762,1000') it is doing the select on empid 6762 whereas my requirement is to get the result set for the query below.
select deptid,name , max(location)
from
emp
where
empid in (6762,1000);
Software/Hardware used:
Software/Hardware used:
ASKED:
March 20, 2009 3:36 AM
UPDATED:
March 20, 2009 2:16 PM
Answer Wiki:
Not sure - rounding!!! not truncating!
my guess would be the sql engine is doing the rounding..
I think that if you had quotes around empnos so the request was sent to sql as
select deptid,name , max(location)
from
emp
where
empid in ('6762,6764');
it would work???
Try hard-coding the select like this '6762,6764' and see if it works
then like this 6762,6764 and see if that works
If the quotes are necessary in the Select then I think you write it something like this..
at least that would be the format elsewhere on the /400 -- four since quotes
start string then double quotes (insert ') then end string.
select deptid,name , max(location)
from
emp
where
empid in ( + '''' + empnos + '''' + );
Phil
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
