469,091 Members | 1,156 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,091 developers. It's quick & easy.

Stored procedure that accepts multiple values

Hi i have a small problem . I need to change one query to stored procedure and call it in my vb.net application.Query is :

Select * from emp where empid in (‘001’,’002’)

For this query I need to create a stored procedure and return rows …and the empID I should give as an input parameter ..can u tell me how to write this in stored proc ??

Create or replace procedure emptest
(
EID IN VARCHAR2,
O_RESULT_SET OUT CURSOR_TYPE
)
As
BEGIN
OPEN O_RESULT_SET FOR
SELECT * from emp where empID in (EID);
End;
/

I can execute this for a single value … 001 but I cant put multiple values like (‘001’,’002’) . it is not accepting … can u tell me how to achieve it ??

I am calling this from my .net client application and using a multiselct list box . so the EID may varry from 1 to N number

Regards
Roshla
Jul 19 '07 #1
4 4745
Hi i have a small problem . I need to change one query to stored procedure and call it in my vb.net application.Query is :

Select * from emp where empid in (‘001’,’002’)

For this query I need to create a stored procedure and return rows …and the empID I should give as an input parameter ..can u tell me how to write this in stored proc ??

Create or replace procedure emptest
(
EID IN VARCHAR2,
O_RESULT_SET OUT CURSOR_TYPE
)
As
BEGIN
OPEN O_RESULT_SET FOR
SELECT * from emp where empID in (EID);
End;
/

I can execute this for a single value … 001 but I cant put multiple values like (‘001’,’002’) . it is not accepting … can u tell me how to achieve it ??

I am calling this from my .net client application and using a multiselct list box . so the EID may varry from 1 to N number

Regards
Roshla
Jul 23 '07 #2
Hi i need help . I need to change one query to stored procedure and call it in my vb.net application.Query is :

Select * from emp where empid in (‘001’,’002’)

For this query I need to create a stored procedure and return rows …and the empID I should give as an input parameter ..can u tell me how to write this in stored proc ??

Create or replace procedure emptest
(
EID IN VARCHAR2,
O_RESULT_SET OUT CURSOR_TYPE
)
As
BEGIN
OPEN O_RESULT_SET FOR
SELECT * from emp where empID in (EID);
End;
/

I can execute this for a single value … 001 but I cant put multiple values like (‘001’,’002’) . it is not accepting … can u tell me how to achieve it ??

I am calling this from my .net client application and using a multiselct list box . so the EID may varry from 1 to N number

Regards
Roshla
Jul 24 '07 #3
debasisdas
8,127 Expert 4TB
Threads merged for better mangement.

MODERATOR
Jul 26 '07 #4
debasisdas
8,127 Expert 4TB
have a varry or table type declared inside the package specification

then have procedure within a package which wil take one value as paramete and add that value to the table type variable

as the table type variable is declared with in the package specification it will ne alive for the whole section

then in the main procedure access the table type variable using the loop extract each value

u have to construct the sql dynamically
Jul 26 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Thomasb | last post: by
9 posts views Thread by fniles | last post: by
4 posts views Thread by yin_n_yang74 | last post: by
2 posts views Thread by jed | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.