Execute multiple procedures in one trip ?

Posted on 2003-11-13
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 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.

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 ?
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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

LVL 20

Assisted Solution

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.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

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

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

717 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