Executing more than one stored procedures in a DataReader

Hey guys,

I have found out that we can execute multiple queries and receive multiple resultsets in a SqlDataReader by executing the queries with ";" separators,

However, what if we wanted to execute two sqlcommand storedprocedures? are there any other way rather than placing "Execute sp1;Execute sp2" in the command text?

I would like to do it in a way whereby I can pass in two storedprocedures with parameters binding capability rather than execute sp1(param1, param2);execute sp2(param1, param2, param3)

Hope to get some suggestions and advice from you guys,

Thank you very much in advance.

Combine the two stored procedures into a third that will execute both.

|||

thanks Mike for your response,

any other better ways?

as there would be too many storedprocs just for combining procedures and it might get messy..

I was thinking to write a custom class and configure the class as I would for Sqlcommand storedprocedure, and then combine the various StoredProc Classes with a ";" and pass into the sqlcommand text.

But would this add-on to more performance defect? as there will be extra objects involved..

Please advice. Thanks.

|||

I don't think by any means or ways its a good idea to execute 2 or more procedures simultaneously from code. If you create such a class which can call and handle 2 or more SPs then also you'll have some questions to answer, such as what about the parameters ? You must be having different parameters for different SPs. Out of them, some may be out put type. What if an error occurs while executing any of the SPs ?.

So, my advice to you is better you execute them one by one if you don't have the compulsion ( which I don't think you'll be having ) to execute them simultaneously.

|||

Hi,

The best way as one user suggested you is to create a stored proc that combines multiple stored procs into one :

For eg:

CREATE PROCEDURE sp_ProcCallMultiple
(
@.var1 varchar(10)
@.var2 varchar(10),
)
AS

EXEC sp_Proc1 @.var1

EXEC sp_Proc2 @.var1

Once done, you can then use sp_ProcCallMultiple using a data reader

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Suprotim Agarwal:

Hi,

The best way as one user suggested you is to create a stored proc that combines multiple stored procs into one :

For eg:

CREATE PROCEDURE sp_ProcCallMultiple
(
@.var1 varchar(10)
@.var2 varchar(10),
)
AS

EXEC sp_Proc1 @.var1

EXEC sp_Proc2 @.var1

Once done, you can then use sp_ProcCallMultiple using a data reader

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

Hi,

Thanks for showing this sample but I already know about this, just wanted to find out if there is an alternative.

To Dhimant: It is possible to handle Output Parameters and so on and it is more effective as it only takes one trip to the server. However, I only need to do this mostly for queries with select statements, other stored procs which require calling two or more procs, i usually execute them in one proc itself. The reason why i didnt want to combine the selection procs is because, there may be too many combinations and things will get messy. Thanks for your advice.