Solved

Execute multiple procedures in one trip ?

Posted on 2003-11-13
10
230 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 10

Expert Comment

by:smegghead
Comment Utility
you can execute something like

exec MyProc1 par1,par2

exec MyProc2 par3,par4
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 10

Expert Comment

by:smegghead
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Bit flags and bit flag manipulation is perhaps one of the most underrated strategies in programming, likely because most programmers developing in high-level languages rely too much on the high-level features, and forget about the low-level ones. Th…
In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now