"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.

Do you mean you're trying to call the procedure using something like this? execute procedure test_in("state1, state2, state3"); If so, I can't think of any way to do that in a stored procedure. If not, call me an idiot and tell me again! :) In article <8bo86v$g7c$1@nnrp1.deja.com>, Curtis Bennett <die_kluge@hotmail.com> wrote: > 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. > -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.

In article <8bo86v$g7c$1@nnrp1.deja.com>, Curtis Bennett <die_kluge@hotmail.com> wrote: > 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 Curtis, If you are using version 9.1x and above I think there is a way to do it using collections and list. I have attempted to put in some code here. Hope this helps. The call to the Stored Procedure is like.... execute function your_procedure_name(a,b,'c','d',"LIST {delimited_string_of_chars}") create function your_procedure_name(a integer, b integer, c char(1),d varchar(32), delimited_string_of_chars LIST(varchar(255) not null)) returning integer; define p integer; define p_char varchar(255); define r integer; define s_s_id integer; define p1 integer; define p2 integer; foreach select * into p_char from TABLE (delimited_string_of_chars) -- p_char contains the first of the string from -- delimited_string_of_chars --SPL logic as per application requirements --check if processor already present and marked end foreach; return 1; end function; -- grant privilege grant execute on your_stored_procedure to public; I am not sure if this will help your cause. However this is ideal for applications that are web enabled which can do some processing while data is captured from the web browser, delimit it and send it to the server for further processing. Cheers Ram S. > > Sent via Deja.com http://www.deja.com/ > Before you buy. > Sent via Deja.com http://www.deja.com/ Before you buy.

mars1972@my-deja.com wrote: > Do you mean you're trying to call the procedure using something like > this? > > execute procedure test_in("state1, state2, state3");> > If so, I can't think of any way to do that in a stored procedure. If > not, call me an idiot and tell me again! :) > > In article <8bo86v$g7c$1@nnrp1.deja.com>, > Curtis Bennett <die_kluge@hotmail.com> wrote: > > 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. > > > -- > # unrm / > ksh: unrm: not found > # man cpio > > Sent via Deja.com http://www.deja.com/ > Before you buy. I would suggest parsing the string into its constituents. In other words, turn the string "state1, state2, state3" into three strings "state1", "state2", "state3". But really, I think using the dynamic SQL capabilities of ESQL/C is better suited for achieving what Curtis is trying to achieve. Cheers, Avi. -- /\\ \\ /| Avi Abrami, Analyst/Programmer, Terayon Comms /__\\ \\ / | eMail: avia@terayon.com / \\ \\/ | http://www.terayon.com