Returning Multiple Tables from Stored Procedure

[Craig G Fraser]

Hi, Does anyone know how to return muliple tables from a stored procedure to an .Net Application. For example if my sp has 3 separate select statements I want to return a dataset containing 3 tables with the results of the 3 select statements. Any help would be greatly appreciated. Cheers, Craig ** I'd rather try and fail than fail to try ;) **

[chinnasrihari]

Hi Create a SP that contains 3 SP (all there should return out put) generate typed dataset and drag and leave the SP on the data set you can see the three tables created on the typed dataset. Or else if you are not using typed dataset. and you are filling to simple data set, after filling data set use like someDataSet.Tables(0) someDataSet.Tables(1) someDataSet.Tables(2) to access the tables data Chinna Srihari Tech Lead, Capgemini

[Michael Potter]

DataSets are perfectly capable of handling multiple tables. There is no difference in acquiring multiple tables from a stored proc as there is in acquiring one table. In your case:

DataSet ds = new DataSet();
// ... Call stored proc
Console.WriteLine("Table 1 Rows: " + ds.Tables[0].Rows.Count().ToString();
Console.WriteLine("Table 2 Rows: " + ds.Tables[1].Rows.Count().ToString();
Console.WriteLine("Table 3 Rows: " + ds.Tab;es[2].Rows.Count().ToString();

[Paul Brower]

The following would return three RESULTSETS from sql server: SELECT * FROM CUSTOMER SELECT * FROM INVOICE SELECT * FROM INVOICE_DETAIL through a SqlCommand object and a SqlDataAdapter you can fill a dataset with the resultset. If you're using a strongly-typed dataset, make sure to set the table mappings. I could have been a little more detailed, but it's all in the MSDN help.