CODEWITHSUNDEEP
sql-serverselectstored-procedures
Mark T
Mark T

Reputation: 823

Naming DataSet.table after performing SQLCommand (Select) Query

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"]

Upvotes: 14

Views: 97249

Answers (4)

davidWazy
davidWazy

Reputation: 61

in stored procedure:

select  CH.PrimaryKey, CH.Name,
        NULL    "CustomerHeader"
from CustomerHeader "CH";
--
select  CD.PrimaryKey, CD.ShipTo,
        NULL    "CustomerDetail"
from CustomerDetail "CD";
--
select  *, NULL "Orders"
from    OrderTable;

in Vb.Net code:

Dim ds As DataSet = Nothing
ds = SqlExecute();
Dim dtCustHeader As DataTable = Nothing
Dim dtCustDetail As DataTable = Nothing
Dim dtOrders As DataTable = Nothing
For Each dt As DataTable In ds.tables
    Select Case True
        Case dt.Columns.Contains("CustomerHeader")
            dtCustHeader = dt
        Case dt.Columns.Contains("CustomerDetail")
            dtCustDetail = dt
        Case dt.Columns.Contains("Orders")
            dtOrders = dt
    End Select
Next

Kinda SILLY (OR STUPID) that you cannot name tables in a result set. But this gets you there without a HUGE byte count repeating the table name within each row.

There is still overhead passing the NULL value back for each row. Perhaps passing a BIT value would be smaller yet...

And an alternative is to always use column(0): in SQL:

select NULL "CustomerDetail", CustName,Addr1,Addr2... from CustomerDetail;

in vb.net:

    Dim ds As DataSet = Nothing
    ds = SqlExecute();
    Dim dtCustHeader As DataTable = Nothing
    Dim dtCustDetail As DataTable = Nothing
    Dim dtOrders As DataTable = Nothing
    For Each dt As DataTable In ds.Tables
        Dim tblName As String = dt.Columns(0).ColumnName
        Select Case tblName.ToUpper
            Case "CUSTOMERDETAIL" : dtCustHeader = dt
            Case "CUSTOMERDETAIL" : dtCustDetail = dt
            Case "ORDERS" : dtOrders = dt
        End Select
    Next

These methods get your table-names even if the query returns zero rows.

but the best for last... a way to actually name the tables in the dataset automatically, every time FROM SQL STORED PROCEDURE (with help from your code):

Dim ds As DataSet = Nothing
ds = SqlExecute();
For Each dt As DataTable In ds.Tables
    dt.TableName = dt.Columns(0).ColumnName
Next

After this, you may access your tables with the name YOU control within the stored procedure... as it should have been from day-one!

EDIT: selective implementation: Name the first column in the pattern "TN:Customer". Your legacy stored procedures work normally, only impacting the stored procedures you wish to modify.

            For Each dt As DataTable In mo_LastDataset.Tables
                Dim tblName() As String = dt.Columns(0).ColumnName.Split(":")
                If tblName.Length >= 2 AndAlso tblName(0).ToUpper = "TN" Then
                    dt.TableName = tblName(1)
                End If
            Next

... david ...

Upvotes: 1

Sophie Sperner
Sophie Sperner

Reputation: 4556

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

Upvotes: -2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

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.

Upvotes: 37

Ocaso Protal
Ocaso Protal

Reputation: 20267

You can use a variable of type table. Read more here: Table Variables In T-SQL

Upvotes: 2

Related Questions