T-SQL动态SQL和临时表

Question

看起来通过EXECUTE字符串方法创建的动态SQL生成的#temptables具有不同的作用域，不能被同一存储过程中的“固定”SQL引用。但是，在后续的动态SQL语句中，我可以引用由动态SQL语句创建的临时表，但似乎存储过程不会返回查询结果到调用客户端，除非SQL是固定的。

简单的两个表情况： 我有两张表。让我们称其为Orders和Items。Order具有OrderId的主键，Items具有ItemId的主键。Items.OrderId是标识父Order的外键。一个Order可以有1到n个项。

我希望能够向用户提供非常灵活的“查询构建器”类型界面，以允许用户选择要查看的项目。筛选条件可以基于Items表和/或父Order表中的字段。如果一个Item符合筛选条件，包括父Order上的条件（如果存在），则该Item应在查询中返回以及父Order。

通常，我认为，大多数人会构建项表和父Order表之间的连接。我想执行2个独立的查询。一个返回所有符合条件的项，另一个返回所有不同的父Order。原因有两个，你可能同意也可能不同意。

第一个原因是，我需要查询父Order表中的所有列，如果我执行单个查询将Orders表与Items表连接起来，我将重复多次Order信息。因为每个订单通常有大量的物品，所以我想避免这种情况，因为这将导致更多的数据传输到fat client上。相反，如上所述，我想在数据集中单独返回两个表，并在其中使用这两个表来填充自定义的Order和child Items客户端对象。（我还不太了解LINQ或Entity Framework。我手动构建我的对象）。第二个原因是我已经有另一个过程，可以返回给定OrderId的所有Items以及父Order，我想使用相同的2表方法，以便我可以重用客户端代码从返回的2个数据表填充我的自定义Order和Client 对象。

我希望能做的是这样的： 在Client上构建一个动态SQL字符串，将orders表连接到Items表，并根据Winform fat-client应用程序上创建的自定义筛选器在每个表上进行适当的筛选。在客户端上构建的SQL可能看起来像这样：

TempSQL = "

    INSERT INTO #ItemsToQuery
       OrderId, ItemsId
    FROM
       Orders, Items 
    WHERE
       Orders.OrderID = Items.OrderId AND
       /* Some unpredictable Order filters go here */
      AND
       /* Some unpredictable Items filters go here */
    "

然后，我会调用一个存储过程。

CREATE PROCEDURE GetItemsAndOrders(@tempSql as text)
   Execute (@tempSQL) --to create the #ItemsToQuery table

SELECT * FROM Items WHERE Items.ItemId IN (SELECT ItemId FROM #ItemsToQuery)

SELECT * FROM Orders WHERE Orders.OrderId IN (SELECT DISTINCT OrderId FROM #ItemsToQuery)

这种方法存在的问题是，#ItemsToQuery表是由动态SQL创建的，因此以下两个静态SQL无法访问它。如果我将静态SQL更改为动态SQL，则不会向fat client返回任何结果。

有三种方法可以解决这个问题，但我正在寻找更好的方法：

1) 可以通过在客户端执行动态构建的SQL来执行第一个SQL语句。然后，结果可以作为表传递给上述存储过程的修改版本。我熟悉将表数据作为XML传递。如果我这样做，存储过程可以使用静态SQL将数据插入临时表中，而该静态SQL由动态SQL创建，因此可以查询而不会出现问题。 (我也可以研究一下是否可以传递新的Table类型参数，而不是XML。) 然而，我想避免向存储过程传递潜在的大型列表。

2) 我可以从客户端执行所有查询。

第一个方法类似于这样：

SELECT Items.* FROM Orders, Items WHERE Order.OrderId = Items.OrderId AND (dynamic filter)
SELECT Orders.* FROM Orders, Items WHERE Order.OrderId = Items.OrderId AND (dynamic filter)

这仍然使我能够重复使用我的客户端对象填充代码，因为订单和物品继续以两个不同的表格返回。

我感觉我可以在存储过程中使用 Table 数据类型的一些选项，但这对我来说也是新的，如果您能给予一点指导，我将不胜感激。

如果你甚至看到了我写的这么远，我很惊讶，但如果您有任何想法如何最好地完成这个任务，我会非常感激。

- Chad

---

你绝对可以从动态SQL返回结果到调用应用程序。这里的大多数答案都涉及到你提出的其他问题。 - Rory

Answer 1

首先需要创建您的表，然后它才会在动态SQL中可用。

这个方法可行：

CREATE TABLE #temp3 (id INT)
EXEC ('insert #temp3 values(1)')

SELECT *
FROM #temp3

这种方法行不通：

EXEC (
        'create table #temp2 (id int)
         insert #temp2 values(1)'
        )

SELECT *
FROM #temp2

换句话说：

1. 创建临时表
2. 执行存储过程
3. 从临时表中选择

这是一个完整的示例：

CREATE PROC prTest2 @var VARCHAR(100)
AS
EXEC (@var)
GO

CREATE TABLE #temp (id INT)

EXEC prTest2 'insert #temp values(1)'

SELECT *
FROM #temp

- SQLMenace

---

我认为这也可以运行：insert into #temptable exec ('select ?? from ??'); - Zachary Scott

如果我们不知道列的定义，那么问题该怎么办？ - Muflix

假设您知道表存在，请从目标表中获取一个顶部1到#someTable。然后截断#someTable。这很快也很简单。免责声明：例如，varchar的列大小可能不代表整个集合中的最大大小。如果您担心这一点，请从sys.tables和sys.columns构建临时表。您只需要执行一次即可。如果您擅长动态SQL，也可以动态完成此操作。 - natur3

Answer 2

我遇到了和 @Muflix 提到的同样的问题。当你不知道返回的列是什么，或者它们是动态生成的时，我所做的是创建一个带有唯一id的全局表，然后在完成后将其删除，代码如下：

DECLARE @DynamicSQL NVARCHAR(MAX)
DECLARE @DynamicTable VARCHAR(255) = 'DynamicTempTable_' + CONVERT(VARCHAR(36), NEWID())
DECLARE @DynamicColumns NVARCHAR(MAX)

--Get "@DynamicColumns", example: SET @DynamicColumns = '[Column1], [Column2]'

SET @DynamicSQL = 'SELECT ' + @DynamicColumns + ' INTO [##' + @DynamicTable + ']' + 
     ' FROM [dbo].[TableXYZ]'

EXEC sp_executesql @DynamicSQL

SET @DynamicSQL = 'IF OBJECT_ID(''tempdb..##' + @DynamicTable + ''' , ''U'') IS NOT NULL ' + 
    ' BEGIN DROP TABLE [##' + @DynamicTable + '] END'

EXEC sp_executesql @DynamicSQL

当然不是最好的解决方案，但这似乎对我有用。

- David Rogers

Answer 3

第一种方法 - 在同一个动态SQL调用中包含多个语句：

DECLARE @DynamicQuery NVARCHAR(MAX)

SET @DynamicQuery = 'Select * into #temp from (select * from tablename) alias 
select * from #temp
drop table #temp'

EXEC sp_executesql @DynamicQuery

第二种方法 - 使用全局临时表：
(注意，您需要特别注意全局变量。)

IF OBJECT_ID('tempdb..##temp2') IS NULL
BEGIN
    EXEC (
            'create table ##temp2 (id int)
             insert ##temp2 values(1)'
            )

    SELECT *
    FROM ##temp2
END

在使用完##temp2对象后，请不要忘记手动删除它：

IF (OBJECT_ID('tempdb..##temp2') IS NOT NULL)
BEGIN
     DROP Table ##temp2
END

注意：如果你不了解数据库的完整结构，请勿使用此方法2。

- Siddhesh

---

这样做不行。在执行动态查询之前，您不能操作表的内容。 - user3335999

Answer 4

动态SQL返回结果集到客户端。我已经做了很多次。

你说得对，通过临时表和变量等方式在SQL和生成的动态SQL之间共享数据存在问题。

我认为，在尝试让你的临时表工作时，你可能会混淆一些东西，因为你绝对可以从执行动态SQL的存储过程中获取数据：

USE SandBox
GO

CREATE PROCEDURE usp_DynTest(@table_type AS VARCHAR(255))
AS 
BEGIN
    DECLARE @sql AS VARCHAR(MAX) = 'SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''' + @table_type + ''''
    EXEC (@sql)
END
GO

EXEC usp_DynTest 'BASE TABLE'
GO

EXEC usp_DynTest 'VIEW'
GO

DROP PROCEDURE usp_DynTest
GO

另外：

USE SandBox
GO

CREATE PROCEDURE usp_DynTest(@table_type AS VARCHAR(255))
AS 
BEGIN
    DECLARE @sql AS VARCHAR(MAX) = 'SELECT * INTO #temp FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''' + @table_type + '''; SELECT * FROM #temp;'
    EXEC (@sql)
END
GO

EXEC usp_DynTest 'BASE TABLE'
GO

EXEC usp_DynTest 'VIEW'
GO

DROP PROCEDURE usp_DynTest
GO

- Cade Roux

---

如果您在过程中创建临时表，它将无法正常工作，您需要先创建临时表，然后才能在过程中填充它。请参阅我的示例。 - SQLMenace

@SQLMenace - 我明白你的意思。我的观点是，您可以从动态SQL返回集合（它们可以使用自己的临时表并从中返回）。我会添加第二个例子。 - Cade Roux

Answer 5

我强烈建议您阅读http://www.sommarskog.se/arrays-in-sql-2005.html。就个人而言，我喜欢传递逗号分隔的文本列表的方法，然后使用文本到表函数进行解析并连接。如果您在连接中首先创建临时表，则可以使用临时表的方法。但这种方法可能会显得有些混乱。

- Sam Saffron

---

我更愿意传递XML而不是CSV。虽然它更冗长，但它允许灵活修改和传递其他列。而且SQL已经知道如何解析XML。但是我看到有将客户端数据集传递到服务器端表变量的示例。非常干净。即使如此，在我看来，这还不如临时表，因为后者的扩展性更差。 - Chad

SQL Server 2008+（或2005？）支持通过客户端提供已定义的表值（从ADO.NET数据集等方面运作良好）。SQL Server 2016+ 支持JSON。 - user2864740