473,322 Members | 1,473 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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;
Jul 19 '05 #1
1 11784

"Berend" <Be**************@evatone.com> wrote in message
news:bd**************************@posting.google.c 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
| 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
Jul 19 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Aaron | last post by:
The following code works fine when previewing a Crystal report using ASP, EXCEPT when it gets to a report using a SubReport and its associated parameters. The whole report just comes up blank with...
4
by: js | last post by:
I have a stored procedure named "processInventory" like the following. Depending on the passed in parameters, I would like to add a WHERE clause for "select" action. For example, if any varchar...
10
by: Resant | last post by:
I have a query : Exec 'Select * From Receiving Where Code In (' + @pCode + ')' @pCode will contain more than one string parameter, eg : A1, A2, A3 How can i write that parameters, I try use :...
39
by: Mike MacSween | last post by:
Just spent a happy 10 mins trying to understand a function I wrote sometime ago. Then remembered that arguments are passed by reference, by default. Does the fact that this slowed me down...
11
by: John Pass | last post by:
Hi, In the attached example, I do understand that the references are not changed if an array is passed by Val. What I do not understand is the result of line 99 (If one can find this by line...
4
by: Ranginald | last post by:
Hi, I'm having trouble passing a parameter from my default.aspx page to my default2.aspx page. I have values from a query in a list box and the goal is to pass the "catID" from default.aspx...
1
by: Berend | last post by:
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...
0
by: Mark C. Stock | last post by:
"Mark C. Stock" <mcstockX@Xenquery .comwrote in message news:... | | "Berend" <Berend.Brinkhuis@evatone.comwrote in message | news:bdd9ac20.0401271301.22cdb65e@posting.google.com... | | I am...
1
by: udaypawar | last post by:
Hi All, I have one problem here with mysql stored procedures. I have a list of ids seperated by comma e.g., (" 'A', 'B', 'C' "). I am passing the same to mysql stored procedure as a parameter....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.