Stored procedure that accepts multiple values

[roshnair]

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

[roshnair]

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

[roshnair]

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

[debasisdas]

Threads merged for better mangement.

MODERATOR

[debasisdas]

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