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

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:...
|
| "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
|
|

here's a reference to the using the TABLE and CAST operators/keywords to feed a the contents of a multi-valued variable to a SQL statement (thanks to another post by dan morgan) http://www.psoug.org/reference/conditions.html

(forget about the temp table and explicit ORs suggestions)

• mcs