From: mcstockX@Xenquery
"Berend" wrote in message
news:bdd9ac20.0401271301.22cdb65e@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
--- SoupGate-Win32 v1.05
* Origin: you cannot sedate... all the things you hate (1:229/2)
|
