T-SQL 动态 SQL 和临时表

2021-12-10 00:00:00 tsql sql-server stored-procedures temp-tables dynamic-sql

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

It looks like #temptables created using dynamic SQL via the EXECUTE string method have a different scope and can't be referenced by "fixed" SQLs in the same stored procedure. However, I can reference a temp table created by a dynamic SQL statement in a subsequence dynamic SQL but it seems that a stored procedure does not return a query result to a calling client unless the SQL is fixed.

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

A simple 2 table scenario: I have 2 tables. Let's call them Orders and Items. Order has a Primary key of OrderId and Items has a Primary Key of ItemId. Items.OrderId is the foreign key to identify the parent Order. An Order can have 1 to n Items.

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

I want to be able to provide a very flexible "query builder" type interface to the user to allow the user to select what Items he want to see. The filter criteria can be based on fields from the Items table and/or from the parent Order table. If an Item meets the filter condition including and condition on the parent Order if one exists, the Item should be return in the query as well as the parent Order.

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

Usually, I suppose, most people would construct a join between the Item table and the parent Order tables. I would like to perform 2 separate queries instead. One to return all of the qualifying Items and the other to return all of the distinct parent Orders. The reason is two fold and you may or may not agree.

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

The first reason is that I need to query all of the columns in the parent Order table and if I did a single query to join the Orders table to the Items table, I would be repoeating the Order information multiple times. Since there are typically a large number of items per Order, I'd like to avoid this because it would result in much more data being transfered to a fat client. Instead, as mentioned, I would like to return the two tables individually in a dataset and use the two tables within to populate a custom Order and child Items client objects. (I don't know enough about LINQ or Entity Framework yet. I build my objects by hand). The second reason I would like to return two tables instead of one is because I already have another procedure that returns all of the Items for a given OrderId along with the parent Order and I would like to use the same 2-table approach so that I could reuse the client code to populate my custom Order and Client objects from the 2 datatables returned.

我希望做的是:

在客户端上构造一个动态 SQL 字符串，它将订单表连接到项目表，并根据在 Winform 胖客户端应用程序上创建的自定义过滤器指定的每个表适当的过滤器.客户端上的 SQL 构建看起来像这样:

Construct a dynamic SQL string on the Client which joins the orders table to the Items table and filters appropriate on each table as specified by the custom filter created on the Winform fat-client app. The SQL build on the client would have looked something like this:

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 */
    "

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

Then, I would call a stored procedure, 

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

The problem with this approach is that #ItemsToQuery table, since it was created by dynamic SQL, is inaccessible from the following 2 static SQLs and if I change the static SQLs to dynamic, no results are passed back to the fat client.

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

3 around come to mind but I'm look for a better one:

1) 第一个 SQL 可以通过从客户端执行动态构造的 SQL 来执行.然后可以将结果作为表传递给上述存储过程的修改版本.我熟悉将表数据作为 XML 传递.如果我这样做，那么存储过程可以使用静态 SQL 将数据插入到临时表中，因为它是由动态 SQL 创建的，然后可以毫无问题地进行查询.(我也可以研究传递新的 Table 类型参数而不是 XML.)但是，我想避免将潜在的大列表传递给存储过程.

1) The first SQL could be performed by executing the dynamically constructed SQL from the client. The results could then be passed as a table to a modified version of the above stored procedure. I am familiar with passing table data as XML. If I did this, the stored proc could then insert the data into a temporary table using a static SQL that, because it was created by dynamic SQL, could then be queried without issue. (I could also investigate into passing the new Table type param instead of XML.) However, I would like to avoid passing up potentially large lists to a stored procedure.

2) 我可以执行来自客户端的所有查询.

2) I could perform all the queries from the client.

第一个是这样的:

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 继续在两个不同的表中返回.

This still provides me with the ability to reuse my client sided object-population code because the Orders and Items continue to be returned in two different tables.

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

I have a feeling to, that I might have some options using a Table data type within my stored proc, but that is also new to me and I would appreciate a little bit of spoon feeding on that one.

如果您甚至将我写的内容扫到了这么远，我会感到惊讶，但如果是这样，我会感谢您对如何做到最好的任何想法.

If you even scanned this far in what I wrote, I am surprised, but if so, I woul dappreciate any of your thoughts on how to accomplish this best.

推荐答案

您首先需要先创建您的表，然后它才能在动态 SQL 中使用.

You first need to create your table first then it will be available in the dynamic 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

相关文章