T-SQL 动态 SQL 和临时表

Question

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

一个简单的 2 表场景： 我有2张桌子。我们将它们称为订单和项目。订单的主键为 OrderId，项目的主键为 ItemId。 Items.OrderId 是标识父订单的外键。一个订单可以有 1 到 n 个项目。

我希望能够向用户提供一个非常灵活的“查询生成器”类型界面，以允许用户选择他想要查看的项目。过滤条件可以基于项目表和/或父订单表中的字段。如果某个项目满足过滤条件，包括父订单上的条件（如果存在），则查询中应返回该项目以及父订单。

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

第一个原因是我需要查询父订单表中的所有列，如果我执行单个查询将订单表连接到项目表，我将多次重复订单信息。由于每个订单通常有大量商品，我想避免这种情况，因为这会导致更多数据传输到胖客户端。相反，如上所述，我想在数据集中单独返回两个表，并使用其中的两个表来填充自定义订单和子项目客户端对象。 （我对 LINQ 或实体框架还不够了解。我手动构建我的对象）。我想返回两个表而不是一个表的第二个原因是因为我已经有另一个过程，该过程返回给定 OrderId 的所有项目以及父订单，并且我想使用相同的 2 表方法，以便我可以重用客户端代码来从返回的 2 个数据表中填充我的自定义订单和客户端对象。

我希望做的是：

在客户端上构造一个动态 SQL 字符串，它将订单表连接到项目表，并按照在 Winform 胖客户端应用程序上创建的自定义过滤器指定的方式对每个表进行适当的过滤。客户端上的 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 创建的，所以无法从以下 2 个静态 SQL 访问，并且如果我将静态 SQL 更改为动态 SQL，则不会将任何结果传回胖客户端。

我想到了 3 个左右，但我正在寻找一个更好的：

1）第一个 SQL 可以通过从客户端执行动态构造的 SQL 来执行。然后，结果可以作为表传递到上述存储过程的修改版本。我熟悉以 XML 形式传递表数据。如果我这样做，存储过程就可以使用静态 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)

这仍然为我提供了重用客户端对象填充代码的能力，因为订单和项目继续在两个不同的表中返回。

我有一种感觉，我可能有一些在存储过程中使用表数据类型的选项，但这对我来说也是新的，我会很感激在这个选项上进行一点点喂养。

如果你浏览了我写的这篇文章，我会感到惊讶，但如果是这样，我会很欣赏你关于如何最好地实现这一目标的任何想法。

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. 执行 proc
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

Answer 2

第一种方法 - 将多个语句包含在同一个动态 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如果您不知道数据库的完整结构。

Answer 3

我遇到了 @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

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

Answer 4

我强烈建议您阅读 http://www.sommarskog.se /arrays-in-sql-2005.html

就我个人而言，我喜欢传递逗号分隔的文本列表，然后将其与文本解析到表函数并连接到它的方法。如果您首先在连接中创建临时表方法，则它可以起作用。但感觉有点乱。

Answer 5

动态 SQL 的结果集返回给客户端。我已经做了很多这样的事了。

关于通过临时表和变量以及 SQL 和它生成的动态 SQL 之间的共享数据之类的问题，您的看法是正确的。

我认为在尝试让临时表工作时，您可能会感到困惑，因为您绝对可以从执行动态 SQL 的 SP 获取数据：

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