Re: Passing multi values into an in clause via a parameter in a store procedure

"Berend" <Berend.Brinkhuis_at_evatone.com> wrote in message news:bdd9ac20.0401271301.22cdb65e_at_posting.google.com...
| I am trying to pass multi values into a where clause with an in clause
| in a store procedure to use in a Crystal report. This can change
| depending on the user. Maybe there is another way to pass multi
| values.
|
|
| CREATE OR REPLACE PROCEDURE eva_sp_wrk014_spec_test (
| p_eva_product_header_ids IN VARCHAR2,
| cur_spec_cd IN OUT
| sysadm.eva_pkg_wrk014_spec_test.ref_spec_spec_cd
| )
| AS
| BEGIN
| OPEN cur_spec_cd
| FOR
| SELECT *
| FROM sysadm.eva_product_header eph
| WHERE eph.eva_product_header_id in (p_eva_product_header_ids);
| END eva_sp_wrk014_spec_test;

the IN clause requires separate values (i.e. a separate bind variable for each value), and you've got all your values stuffed into one variable, the equivalent of

WHERE eph.eva_product_header_id in ( '00,01,23,43,09,33' )

which attempts to find the value '00,01,23,43,09,33' not the one of the individual values

you'll need to rewrite your proc to use dynamic sql, or pull a trick like

WHERE instr( p_eva_product_header_ids, cDelimiter || eph.eva_product_header_id || cDelimiter) > 0

(the p_eva_product_header_ids parameter would need to have delimiters between each value, and at the first and last position) -- not great for performance if it's the only criteria

other options: write the list of IDs to a temp table (perhaps within the proc itself) and use a subquery or join to the temp table

i think there's also a way these days to write a proc that returns a rowset that can be used as a SQL table -- that might be another way to transform the common separated list of ids into something useful in a non-dynamic SQL statement

or -- write a bunch of explicit ORs that can handle up to the max number or IDs you think you'd be searching for, then parse out the IDs into local variables

• mcs