sql-server - T-SQL 动态 SQL 和临时表

Question

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

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

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

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

第一个原因是我需要查询父 Order 表中的所有列，如果我执行单个查询将 Orders 表连接到 Items 表，我将多次重复 Order 信息。由于每个订单通常有大量项目，我想避免这种情况，因为它会导致更多数据传输到胖客户端。相反，如前所述，我想分别返回数据集中的两个表，并使用其中的两个表来填充自定义 Order 和 child Items 客户端对象。 (我对 LINQ 或 Entity Framework 还不够了解。我手工构建我的对象)。我想返回两个表而不是一个表的第二个原因是因为我已经有另一个过程可以返回给定 OrderId 的所有项目以及父订单，我想使用相同的 2-table 方法，以便我可以重用客户端代码从返回的 2 个数据表中填充我的自定义 Order 和 Client 对象。

我希望做的是这样的:

在客户端上构造一个动态 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)

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

我有一种感觉，在我的存储过程中，我可能有一些使用 Table 数据类型的选项，但这对我来说也是新的，我希望能用一点勺子喂它。

如果你甚至在我写的内容中浏览了这么远，我会感到惊讶，但如果是这样，我会感谢你关于如何做到最好的任何想法。

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

换句话说:

创建临时表

执行 proc

从临时表中选择

这是完整的示例:

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

CREATE TABLE #temp (id INT)

EXEC prTest2 'insert #temp values(1)'

SELECT *
FROM #temp