Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

Execute multiple procedures in one trip ?

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
russ100
Asked:
russ100
3 Solutions
 
smeggheadCommented:
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
 
SRigneyCommented:
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
 
russ100Author Commented:
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
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.

 
smeggheadCommented:
you can execute something like

exec MyProc1 par1,par2

exec MyProc2 par3,par4
0
 
smeggheadCommented:
sorry, that's not clear

Run the following command, passing in par1-4

exec MyProc1 @par1,@par2
exec MyProc2 @par3,@par4
0
 
russ100Author Commented:
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
 
smeggheadCommented:
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
 
TheAvengerCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now