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?

Solution 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?

Solution 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");

Solution 3

Unfortunately, I do not believe this is possible! I have a similar setup which gets DataSets from Stored Procedures, and after looking I gave up and resorted to indexes.

Solution 4

This is also not the best solution, but you could make the first column in your query be the table name:

    Select 'Customer', CustomerID, CustomerName, CustomerAddress
    From Customer
    Where CustomerID = @CustomerID

    Select 'Orders', OrderID, OrderPrice, OrderDate
    From Order O
    Join Customer C on C.CustomerID = O.CustomerID
    Where C.CustomerID = @CustomerID

    Select 'OrderItems', ItemID, ItemDescription, ItemPrice
    From OrderItems I
    Join Order O on O.OrderID = I.OrderID
    Join Customer C on C.CustomerID = O.CustomerID
    Where C.CustomerID = @CustomerID

Solution 5

It is not possible, but its SQL "fault", not the fault of DataAdapter/Set, because result set does not carry the name of the table queried (nor is that discernibly possible if you use inner join) nor does the table adapter have a query from which to pick the name. One method you can use is to first return a list of tables as Query#0 in the procedure, e.g.

select 'MyTable;MySecondTable;ThirdOrSo' as tables

followed by all other queries, then read index 0 table and this field, split/forloop to rename other tables in dataset. The maintainer would still need to know the mechanism but at least it gives him some freedom to reorganize..

View more solutions

Comments

[mmix]

This doesn't solve the initial problem of the post, ability of the procedure maintainer to not worry about the order of queries.

[Bradley Smith]

Thanks; your suggestion helps to a certain extent, although it's not ideal. At least this places responsibility for dealing with any mistakes firmly with the database, rather than the client app.

[Joe]

It doesn't give him exactly what he's asking for: he still needs to code his SP to return result sets in a specific order (a reasonable restriction in my view). But afterwards he can access the tables in the resulting DataSet by name rather than index, and restrict knowledge of the order in which the results are returned to the DAL class that fills the DataSet.

[Bradley Smith]

That would solve the issue, but it still places the onice on the person maintaining the procedure to follow that convention. I was hoping for a tighter solution, something directly supported by T-SQL, but it seems like no such feature exists.