Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Aug 09, 2010 10:04 AM by ajay_sanhotra
Aug 06, 2010 06:19 AM|LINK
I have a small query can we store three different stored procedure data into single dataset(in multiple tables).
I am asking this because i have three stored procedure and when i run them simultaneously it takes less time to execute and when i run them separately it takes more time and i want to use all three stored procedure in page load.
environment - Sql server 2005, ASP.net 2.0
Aug 08, 2010 09:08 AM|LINK
Adjust your stored procedure to return multiple datasets
CREATE PROCEDURE ...
SELECT * FROM Table1
SELECT * FROM Table2
SELECT * FROM Table3
In .NET you can get all 3 tables using DataSet and the Table property or SqlDataReader and its NextResult method as follows
SqlDataReader dr = ...
dr.NextResult(); <--- Here you go to second dataset
dr.NextResult(); <--- Here you go to third dataset
Hope this helps.
Aug 08, 2010 06:30 PM|LINK
you can execute four queries one by one in order.
Then, your dataset will be populated by four datatables.
you can also create a procedure with four select statements which produces the same result.
With Table Names Table1, Table2, Table 3 and you can add Data Table Mappings to give some meaningful names
try that and see if it works
Aug 09, 2010 05:59 AM|LINK
Thank you guys for he reply,
your sugesstions are very helpful
but my problem is my three stored procedures are very lengthy and each stored procedure calling four function,i not want to merge all three stored procedure select query into single stored procedure,as some other guys are using either of the stored procedure
in there respective form.
I want to get the three stored procedure values into single dataset, for that i have to execute three procedure seperatley and merge into single dataset,but that not possible as to merge dataset it should have same value.
please help to find way in this direction.
Aug 09, 2010 06:07 AM|LINK
The thing is you do not need to merge the procedures you can just create one procedure for your purpose and call the other three procedures in that.
Create procedure myprocedure
-- call the procedure here
exec firstProcedure parametersifany;
exec secondProcedure parametersifany;
exec thirdProcedure parametersifany;
so, you are not touching the other procedures, you are just calling them from your procedure.
That should get your data into three data tables in your dataset.
Think about it.
Aug 09, 2010 06:26 AM|LINK
Hey thanks this seems to work for me, i will try this now and hope this will work fine
again thanks a lot
Aug 09, 2010 06:31 AM|LINK
Try that and see if it works.
make sure you pass parameters if the procedure has any parameters.
Exec ProcedureName is for a procedure without parameters
Exec ProcedureName Param1Value is for a procedure with parameter.
let me know if you need any help
Aug 09, 2010 10:04 AM|LINK
Hi santosh,i have successfully done that,
i have marked your suggestion as answer, thanx again