T-SQL动态SQL和临时表 [英] T-SQL Dynamic SQL and Temp Tables

问题描述

看起来，通过EXECUTE字符串方法使用动态SQL创建的#temptable具有不同的范围，并且不能在同一存储过程中被固定 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张桌子。我们称它们为订单和物品。订单的主键为OrderId，项目的主键为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.

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

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信息。由于每个订单通常有大量商品，因此我想避免这种情况，因为这将导致更多数据传输到胖客户端。相反，如上所述，我想分别返回数据集中的两个表，并使用其中的两个表填充自定义Order和child Items客户端对象。 （我对LINQ或Entity Framework的了解还不够。我手动构建对象）。我想返回两个表而不是一个表的第二个原因是因为我已经有了另一个过程，该过程返回给定OrderId的所有Items以及父Order，并且我想使用相同的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字符串，该字符串将orders表连接到Items表，并根据Winform fat-client应用程序上创建的自定义过滤器的指定，在每个表上适当的过滤器。在客户端上构建的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创建的，因此无法从以下两个站点访问tic SQL，如果我将静态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传递。如果执行此操作，则存储的proc可以使用静态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.

我有一种感觉，我可能在存储的proc中使用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.

锡s的作品：

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

这篇关于T-SQL动态SQL和临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！