RE: "IN" clause via a single parameter in a SP
If I call
execute procedure test_in("'AR', 'AZ', 'AL'") ;
I get 'no rows found'
But if I call a separate procedure that takes 3 arguments of char(2)
execute procedure test_in('AR', 'AZ', 'AL') ;I get 3 states.
Subsequently, if I call the first example and just pass it one state
execute procedure test_in("AR") ;I get a row back.
If I run
execute procedure test_in("'AR'") ; I get nothing.
Weird.
From: Scott Black <sblack@elsouth.com>
Subject: RE: "IN" clause via a single parameter in a SP
Date: Mon, 27 Mar 2000 13:40:13 -0500
I'm guessing it has something to do with quotes. Since it works for:
where state_code in (state_1, state_2, state_3....)
but not for:
where state_code in (state_str)
The engine is probably treating state_str as one whole value. Have you
tried playing around with the quotes i.e.
execute procedure test_in("'state1', 'state2', 'state3'");?
I think the trick will be to make the engine see state_str as separate
values, instead of one. And I'm not sure there's a way to do it... but
I'd like to hear about if you figure it out.
HTH
-----Original Message-----
From: Curtis Bennett [mailto:die_kluge@hotmail.com]
Sent: Monday, March 27, 2000 1:11 PM
Posted To: informix
Conversation: "IN" clause via a single parameter in a SP
Subject: "IN" clause via a single parameter in a SP
I'm tring to write a stored procedure that takes a parameter of a
string, say size char(200) or so. And with pseudo-code, this is what
I'm trying to do :
create procedure test_in (state_str char(200))
returning char(50)
select state_nm
into st_nm
from states
where state_code in (state_str)
return st_nm
with resume ;
end procedure ;
It doesn't work. I mean, I can run it, but I get no rows returned. If
I pass it two or more parameters, and then do
...
where state_code in (state_1, state_2, state_3....)
it works. But not the above example. I'd really rather not have to
take 50 parameters into this procedure to get it to do this. Am I
overlooking something here?
Thanks,
--
Curtis Bennett
CIBER, INC
Overland Park, KS
Sent via Deja.com http://www.deja.com/
Before you buy.
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com