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"]
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.
You can use a variable of type table. Read more here: Table Variables In T-SQL
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 ...
SELECT * AS MyTableName
FROM ContentReportRequests a, UserPreferences d
WHERE a.UserID = d.UserID and a.ID =@ID
