Assign names to tables in an SQL Server result set

Question

I am writing a stored procedure that executes several successive SELECT statements. When I execute this procedure via ADO.NET, my intention is to end up with a DataSet containing several DataTable objects. This behaves as expected.

I am currently relying on the order of the tables in the DataSet to match the order of the SELECT statements in the stored procedure, however there is really no significance in this order. The person who ultimately has to maintain the procedure shouldn't have to know the expected order of the results, nor should the person maintaining the application have to know the order of the statements in the procedure.

What I want to know is, is it possible to assign names to the result of each SELECT statement within the stored procedure itself, and then have these come through via ADO.NET (hopefully seamlessly) so that I can access each table by its name instead of its order?

e.g.

// populate DataSet with results from stored proc
DataSet ds = new DataSet();
dataAdapter.Fill(ds);

// now access one of the resulting DataTable via name
return ds.Tables["NamedResultFromTheProc"];

So, is there any way to achieve this? Or will I have to rely on the order of the SELECT statements and always access the desired table by its index?

Answer 1

I've not tried this but could you not change the structure of the stored proc so that you have a query returning the name of the table before each data query?

i.e.

select 'TableName'
select * from Table where 1 = 1

then build the Dataset manually by creating tables and adding them in?

Answer 2

The tables returned by your query will be given the names "Table", "Table1", "Table2" etc.

You can add TableMappings to your DataAdapter before filling your DataSet to map them to your table names:

myAdapter.TableMappings.Add("Table", "MyTable1");
myAdapter.TableMappings.Add("Table1", "MyTable2");
myAdapter.TableMappings.Add("Table2", "MyTable3");