AS/400 stored procedure passing parameter

160 pts.
Tags:
AS/400 development
AS/400 stored procedures
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);

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following