T-SQL 动态 SQL 和临时表

看起来通过 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 更改为动态，则不会将任何结果传递回胖客户端。

我想到了 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)

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

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

如果您浏览了我写的这篇文章，我会感到惊讶，但如果是这样，我将不胜感激您对如何最好地实现这一目标的任何想法。

您首先需要创建表，然后它将在动态 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