"Mark C. Stock" <mcstockX@Xenqu ery .comwrote in message news:...
|
| "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
|
|
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