By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,742 Members | 1,570 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,742 IT Pros & Developers. It's quick & easy.

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

P: n/a

"Mark C. Stock" <mcstockX@Xenquery .comwrote in message news:...
| "Berend" <Be**************@evatone.comwrote in message
| news:bd************************** om...
| | 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
| | 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
| 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
| statement
| or -- write a bunch of explicit ORs that can handle up to the max number
| 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)

(forget about the temp table and explicit ORs suggestions)

-- mcs
Jun 27 '08 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.