Has anyone ever done this?

Ok, my requirement is to write a procedure which takes n number of states as a parameter. So, my original assertion was that I could just use a large variable of states and use it like an "in" clause. ie (pseudocode) create procedure find_states(state_str as char(255)) returning char(20); foreach state_cursor select state_desc into description from states where state_code in (state_str) return description with resume ; end foreach ; end procedure; And, of course, like all logical things, it doesn't work. Regardless of how I format state_str. I tried several variations. So, my next attempt involves writing a user-defined routine which acts more or less like strtok in C. I pass it a string of up to 255 and a delimiter of size 1, and I expect many strings to spew forth from it. So I could do something like this (theoretically) : select token("car|boat|truck|bike|plane|". "|") from irrelevant_table ; car boat truck ... What I'm looking for is if anyone has ever done anything like. If anyone knows of an easier way to do this. Or if anyone has any UDR code that might step me in the right direction on something like this. Or if anyone has any input as to whether or not the above will even work before I head down this path. As a side note, I tried doing a while loop in a procedure to parse out the state_str manually, but in 14-22T I see the line "Subscripts must always be constants." So, you can't do something like : state = state_str[i,2] ; It complains about the 'i'. Thanks. I love this list, btw. -- Curtis Bennett CIBER, INC Overland Park, KS Sent via Deja.com http://www.deja.com/ Before you buy.

This should work : create procedure find_states(state_str char(255)) returning char(20);define description like states.state_desc; foreach select state_desc into description from states where (state_str) matches '*' || state_code || '*' return description with resume ; end foreach ; end procedure; Needless to say, state_str should contain delimiters i.e it should look like "CA,ON,BC", rather than "CAONBC". Rudy Curtis Bennett wrote: > Ok, my requirement is to write a procedure which takes n number of > states as a parameter. So, my original assertion was that I could just > use a large variable of states and use it like an "in" clause. > > ie (pseudocode) > create procedure find_states(state_str as char(255)) returning char(20);> foreach state_cursor > select state_desc into description > from states > where state_code in (state_str) > return description with resume ; > end foreach ; > end procedure; > > ...