Re: passing variables in stored procedures

garygte@my-dejanews.com wrote: > > I am working in Informix 7.2 and I have two question. Queston - 1: I have a > stored procedure that accepts 4 variables. two characters and two integers. > The second character is State code. Part of the new modifications to the > report is to allow the end user to select multiple state codes, for example > FL, GA, AL, MS. I would like to know how I could go about coding to allow the > stored procedure to accept this dynamic string and equate it in the stored > procedure 'where clause' to read ...WHERE state_code IN ('FL', 'GA', 'Al', > 'MS')... I don't think this is possible as it requires dynamic SQL; not possible in stored procedures. > > Question - 2: > Assuming there is no solution to question 1, here is the second one: > How do I parse the above string into individual variables, in a temp table? > Try something like this (not tested but you get the idea): DEFINE s CHAR(4); -- remove trailing blanks, then comma LET s = TRIM(TRAILING ',' FROM TRIM(state[1,4])); WHILE s != ' ' -- insert into temp table... LET state = state[5,80]; LET s = TRIM(TRAILING ',' FROM TRIM(state[1,4])); END WHILE; -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions and the release notes. Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/ ---