Executing multiple stored procedures in a single batch

  • Thread starter Thread starter Venkatesh
  • Start date Start date
V

Venkatesh

Hi

I want to execute multiple SQL Server stored procedures in
a single batch via ADO so that I can minimize network
trips. I need to pass in an array of caommandtype and
commandtext objects. What is the best way to do this. Is
there an example anywhere on how to do this ?

Thanks,

Venkatesh
 
Stored procedures can call stored procedures.
You can also submit a script that contains several SP calls (or other TSQL
code) and deal with the resultsets one-at-a-time. Simply construct a long
string containing the TSQL and use ExecuteNonQuery or Fill to execute it.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I will need to execute several parametrized stored
procedures individually. What I am looking for is a method
by which I can fill a collection of commandtext,command
type and parameter objects , construct a string and call
all the stored procedures at once without having to make
multiple round trips to the server.
 
CommandText = "SPfirst p1,p2,p3 EXEC SPNext p1, p2 EXEC SPlast p1 "

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Thanks Bill. How do I implement the commandtext and
command parameters collections ? In other worrds, how do I
associate the parameters with the relevant stored
procedure ?
 
Nope, I was wrong. I thought this would work and it does not. ADO.NET won't
correctly parse more than one SP invocation in a single Command object.

However, you can call a single SP that calls several others on the server
end. In this case the Command object is setup normally.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top