Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Execute multiple procedures in one trip ?

Posted on 2003-11-13
10
232 Views
Last Modified: 2010-04-16
I know you can execute more than one select statement in one trip to the database, but is it possible to execute more than one non-query statement at a time - can you still set parameters to both ?
0
Comment
Question by:russ100
10 Comments
 
LVL 10

Expert Comment

by:smegghead
ID: 9738900
yes (I'm assuming you're talking about sql-server??)

As long as the parameter names are unique, then it will be fine.

Smg.

0
 
LVL 15

Accepted Solution

by:
SRigney earned 43 total points
ID: 9739144
yes, what you'll want is a single stored procedure that runs multiple update statements within it.  All of the parameters that are needed for everything will need to be passed to the stored procedure.  

If it's a large chunk of data, or is a variable amount of data.  One parent with possibly multiple children, then you will want to look into passing an xmldocument and using OPENXML in your stored procedure.  (SQL2000 only).

If you are on an older version of SQL you can pass variable length data into a string, but your limited on the size much more.
0
 

Author Comment

by:russ100
ID: 9739272
Im sorry I did not elaborate on the title of the post in the body, but what I meant was - is it possible to execute 2 stored procedures in on command call ?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 10

Expert Comment

by:smegghead
ID: 9739311
you can execute something like

exec MyProc1 par1,par2

exec MyProc2 par3,par4
0
 
LVL 10

Expert Comment

by:smegghead
ID: 9739323
sorry, that's not clear

Run the following command, passing in par1-4

exec MyProc1 @par1,@par2
exec MyProc2 @par3,@par4
0
 

Author Comment

by:russ100
ID: 9739657
ok, the code so far .....

OleDbConnection Oraclecon = new OleDbConnection("Provider=MSDAORA.1;Password=xxx;User ID=yyy;Data Source=xyxyxy");
Oraclecon.Open();
OleDbCommand myCMD = new OleDbCommand("packagename.procedurename", Oraclecon);
myCMD.CommandType = CommandType.StoredProcedure;
                        
myCMD.Parameters.Add("param1",OleDbType.VarChar,16).Value = "param_val";

int i;
i = myCMD.ExecuteNonQuery();
Oraclecon.Close();

What I want to do now is call two stored procedures instead of one.

Any ideas ?
0
 
LVL 10

Assisted Solution

by:smegghead
smegghead earned 41 total points
ID: 9739708
yes, but you have to contain both within one command/stored procedure

so you have one sp which calls the other two.

You didn't mention that you were using oracle... so I'm not 100% on the syntax, but it should be something like

You have 1 stored procedure called 'CallBoth' or whatever. Which has the following code..

exec Proc1 @par1,@par2
exec Proc2 @par3,@par4

0
 
LVL 20

Assisted Solution

by:TheAvenger
TheAvenger earned 41 total points
ID: 9740889
You should change the type of the command to Text, not stored prodedure. Then you can put the two stored procedures as mentioned by the other guys, maybe with a separator between them (for SQL server it's ; but for Oracle I don't know). Then you can execute everything perfectly and in the result of ExecuteNonQuery you will get the result of the last stored procedure.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question