Naming DataSet.table after performing SQLCommand (Select) Query

Question

In stored procedure MS SQL My query is:

SELECT *  
FROM ContentReportRequests a,UserPreferences d
WHERE  a.UserID = d.UserID and a.ID =@ID

I want to give the result table some name. How can I do this ?

I want to pull it to ADO.Net DataSet.tables["NAME"]

Answer 1

I can imagine a few things you might be meaning.

If you want to persist this result set, for consumption in multiple later queries, you might be looking for SELECT INTO:

SELECT * into NewTableName
FROM ContentReportRequests a,UserPreferences d
WHERE  a.UserID = d.UserID and a.ID =@ID

Where NewTableName is a new name, and a new (permanent) table will be created. If you want that table to go away when you're finished, prefix the name with a #, to make it a temp table.

Alternatively, you might just be wanting to absorb it into a single larger query, in which case you'd be looking at making it a subselect:

SELECT *
FROM (SELECT *  
FROM ContentReportRequests a,UserPreferences d
WHERE  a.UserID = d.UserID and a.ID =@ID
) NewTableName
WHERE NewTableName.ColumnValue = 'abc'

or a CTE:

WITH NewTableName AS (
    SELECT *  
    FROM ContentReportRequests a,UserPreferences d
    WHERE  a.UserID = d.UserID and a.ID =@ID
)
SELECT * from NewTableName

Finally, you might be talking about pulling the result set into e.g. an ADO.Net DataTable, and you want the name to be set automatically. I'm not sure that that is feasible.