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?

In my company, we have an internal tool which runs SQL scripts and displays the results to the user. Multiple tables in the result set are shown in tabs. I was just thinking it would be cool if we could give meaningful names to the tabs by assigning names to the result tables. — Stewart, Dec 05 '22 at 16:30

score 6 · Accepted Answer · edited Dec 05 '22 at 16:25

6

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?

edited Dec 05 '22 at 16:25

Stewart

3,935
4
27
36

answered Feb 04 '11 at 10:50

Dave

3,581
1
22
25

1

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. – Bradley Smith Feb 04 '11 at 11:21

score 4 · Answer 2 · answered Feb 04 '11 at 09:27

4

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

answered Feb 04 '11 at 09:27

Joe

122,218
32
205
338

3

This doesn't solve the initial problem of the post, ability of the procedure maintainer to not worry about the order of queries. – mmix Feb 04 '11 at 10:12
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. – Joe Feb 04 '11 at 11:12

score 2 · Answer 3 · edited Dec 05 '22 at 16:26

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;

This depends on whether adding an extra field would break the way the result set is being used. — Stewart, Dec 05 '22 at 16:26

score 2 · Answer 4 · answered Feb 04 '11 at 08:20

2

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.

answered Feb 04 '11 at 08:20

Tom

3,354
1
22
25

score 1 · Answer 5 · answered Feb 04 '11 at 08:38

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..

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. — Bradley Smith, Feb 04 '11 at 10:41

score 0 · Answer 6 · answered Jun 29 '12 at 09:37

I've been thinking about this as well and the only solution I can think of is to create temporary tables within the procedure and populate the results into there (naming the tables as you go).

I've not tried this yet because it doesn't feel like the right way to do it with having to get the results twice (query into temp table, query the temp table).

It would be really useful if you could just rename your result set in SQL in the same way you can rename "Column AS [Custom Column]"...

Assign names to tables in an SQL Server result set

6 Answers6