[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Execute multiple procedures in one trip ?

Posted on 2003-11-13
Medium Priority
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 ?
Question by:russ100
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
LVL 10

Expert Comment

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.


LVL 15

Accepted Solution

SRigney earned 129 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.

Author Comment

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 ?
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 10

Expert Comment

ID: 9739311
you can execute something like

exec MyProc1 par1,par2

exec MyProc2 par3,par4
LVL 10

Expert Comment

ID: 9739323
sorry, that's not clear

Run the following command, passing in par1-4

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

Author Comment

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

OleDbConnection Oraclecon = new OleDbConnection("Provider=MSDAORA.1;Password=xxx;User ID=yyy;Data Source=xyxyxy");
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();

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

Any ideas ?
LVL 10

Assisted Solution

smegghead earned 123 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

LVL 20

Assisted Solution

TheAvenger earned 123 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.

Featured Post

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

656 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