选择后给表命名

In stored procedure MS SQL My query is:

在MS SQL存储过程中，我的查询是:

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

我想拉它到ADO。净DataSet.tables(“名称”)

4 个解决方案

#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:

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.

NewTableName是一个新名称，并将创建一个新的(永久性)表。如果您希望该表在完成时消失，请在名称前面加上#，使其成为临时表。

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:

或一个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.

最后，你可能正在谈论将结果集转换成一个ADO。Net DataTable，您需要自动设置名称。我不确定这是否可行。

#2

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

您可以使用类型表的变量。请阅读本文:T-SQL中的表变量

#3

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:

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

对于每一行，仍然存在传递NULL值的开销。也许传递一个比特值会更小……

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

另一种选择是始终使用(0)列:在SQL中:

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

in vb.net:

在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):

但最好的最后……一种每次从SQL存储过程(在代码的帮助下)自动命名数据集中的表的方法:

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.

编辑:选择实现:将模式中的第一列命名为“TN:Customer”。您的遗留存储过程正常工作，只影响您希望修改的存储过程。

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

…大卫……

#4

-2

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

#1