If your SP's very fast to populate you may want to consider only making one pass at the DB. You could add all your parameters at once if possible and load all of your result sets into a DataSet on your web server. You can then check your dataset results
and execute your conditional codes based on the values in your datatables inside your dataset. Your dataset would then act like a temporary database housing the temporary tables you created with one pass on your DB.
I personally prefer to limit the number of times I need to hit the database on a page. By using a dataset with several datatables populated by your SP you would only have to hit the DB once to pull all your data.
So your SP would Fill your Dataset, in the order your result sets are created.
So Query1 in the SP would be DataSet.DataTable(0)
Query2 to would be DataSet.DataTable(1) and so on
There isn't really anything "wrong" with the way your doing it now, but by retreiving all of your records in one pass on the Database if possible, you limit the number of connections being made to the DB which is normally a good thing :)
Marked as answer by Ming Xu - MSFT on May 07, 2012 10:11 AM
Loganix77
Participant
1351 Points
412 Posts
Re: What is the better way to call multiple Stored Procedures
Apr 30, 2012 02:13 PM|LINK
If your SP's very fast to populate you may want to consider only making one pass at the DB. You could add all your parameters at once if possible and load all of your result sets into a DataSet on your web server. You can then check your dataset results and execute your conditional codes based on the values in your datatables inside your dataset. Your dataset would then act like a temporary database housing the temporary tables you created with one pass on your DB.
I personally prefer to limit the number of times I need to hit the database on a page. By using a dataset with several datatables populated by your SP you would only have to hit the DB once to pull all your data.
So your SP would Fill your Dataset, in the order your result sets are created.
So Query1 in the SP would be DataSet.DataTable(0)
Query2 to would be DataSet.DataTable(1) and so on
There isn't really anything "wrong" with the way your doing it now, but by retreiving all of your records in one pass on the Database if possible, you limit the number of connections being made to the DB which is normally a good thing :)