473,326 Members | 2,134 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,326 software developers and data experts.

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 5130
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

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

Similar topics

4
by: randy.p.ho | last post by:
Using JDBC, is there a way to call a stored procedure with multiple return values? Thanks.
8
by: Thomasb | last post by:
With a background in MS SQL Server programming I'm used to temporary tables. Have just started to work with DB2 ver 7 on z/OS and stumbled into the concept of GLOBAL TEMPORARY TABLE. I have...
28
by: mooreit | last post by:
The purpose for my questions is accessing these technologies from applications. I develop both applications and databases. Working with Microsoft C#.NET and Microsoft SQL Server 2000 Production and...
9
by: anilcool | last post by:
Hi all, Another novice question. I want to insert multiple records into my DB2 database using stored procedure but I do not know how many I would like to insert at any given time. Each record...
7
by: Dabbler | last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the following error when trying to update (ExecuteNonQuery): System.Data.SqlClient.SqlException: Procedure or Function...
1
by: deepdata | last post by:
Hi, I am trying to fetch data from db2 (express version) database by calling stored procedure. I have tried to use both cursor and for loop but still i am getting error. --======Start...
9
by: fniles | last post by:
I am using VB.NET 2003 and SQL2000 database. I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1 parameter (varchar(10)) and returns the identity column value from that table....
4
by: yin_n_yang74 | last post by:
I am new to SQL and SQL Server world. There must be a simple solution to this, but I'm not seeing it. I am trying to create a crystal report (v8.5) using a stored procedure from SQL Server...
2
by: jed | last post by:
I have created this example in sqlexpress ALTER PROCEDURE . @annualtax FLOAT AS BEGIN SELECT begin1,end1,deductedamount,pecentageextra FROM tax
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
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
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

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.