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

[Berend]

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_s pec_test (
p_eva_product_h eader_ids IN VARCHAR2,
cur_spec_cd IN OUT
sysadm.eva_pkg_ wrk014_spec_tes t.ref_spec_spec _cd
)
AS
BEGIN
OPEN cur_spec_cd
FOR
SELECT *
FROM sysadm.eva_prod uct_header eph
WHERE eph.eva_product _header_id in (p_eva_product_ header_ids);
END eva_sp_wrk014_s pec_test;

[Mark C. Stock]

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


"Berend" <Berend.Brinkhu is@evatone.comw rote in message
news:bdd9ac20.0 401271301.22cdb 65e@posting.goo gle.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_s pec_test (
| p_eva_product_h eader_ids IN VARCHAR2,
| cur_spec_cd IN OUT
| sysadm.eva_pkg_ wrk014_spec_tes t.ref_spec_spec _cd
| )
| AS
| BEGIN
| OPEN cur_spec_cd
| FOR
| SELECT *
| FROM sysadm.eva_prod uct_header eph
| WHERE eph.eva_product _header_id in (p_eva_product_ header_ids);
| END eva_sp_wrk014_s pec_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_h eader_ids, cDelimiter ||
eph.eva_product _header_id || cDelimiter) 0

(the p_eva_product_h eader_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