T-SQL Dynamic SQL и временные таблицы

Похоже, что #temptables, созданные с использованием динамического SQL с помощью строкового метода EXECUTE, имеют другую область видимости, и на них нельзя ссылаться с помощью «фиксированных» SQL в той же хранимой процедуре. Однако я могу ссылаться на временную таблицу, созданную динамическим оператором SQL в подпоследовательности динамического SQL, но кажется, что хранимая процедура не возвращает результат запроса вызывающему клиенту, если SQL не исправлен.

Простой сценарий с двумя таблицами: у меня есть 2 стола. Назовем их "Заказы" и "Предметы". У Order первичный ключ OrderId, а у Items первичный ключ ItemId. Items.OrderId - это внешний ключ для идентификации родительского Order. В заказе может быть от 1 до n элементов.

Я хочу иметь возможность предоставить пользователю очень гибкий интерфейс типа «построитель запросов», чтобы пользователь мог выбирать, какие элементы он хочет видеть. Критерии фильтрации могут быть основаны на полях из таблицы Items и / или из родительской таблицы Order. Если элемент соответствует условию фильтрации, включая и условие для родительского заказа, если он существует, элемент должен быть возвращен в запросе, а также родительский заказ.

Обычно, я полагаю, большинство людей создают соединение между таблицей элементов и родительскими таблицами Order. Вместо этого я хотел бы выполнить 2 отдельных запроса. Один для возврата всех соответствующих элементов, а другой - для возврата всех отдельных родительских заказов. Причина двоякая, и вы можете согласиться, а можете и не согласиться.

Первая причина заключается в том, что мне нужно запросить все столбцы в родительской таблице Order, и если бы я сделал один запрос для присоединения таблицы Orders к таблице Items, я бы повторно отправил информацию о заказе несколько раз. Поскольку обычно в одном Заказе много товаров, я бы хотел избежать этого, потому что это привело бы к передаче гораздо большего количества данных толстому клиенту. Вместо этого, как уже упоминалось, я хотел бы вернуть две таблицы по отдельности в наборе данных и использовать две таблицы внутри для заполнения пользовательских клиентских объектов Order и дочерних элементов. (Я еще недостаточно знаю LINQ или Entity Framework. Я создаю свои объекты вручную). Вторая причина, по которой я хотел бы вернуть две таблицы вместо одной, заключается в том, что у меня уже есть другая процедура, которая возвращает все элементы для данного OrderId вместе с родительским Order, и я хотел бы использовать тот же подход с двумя таблицами, чтобы я мог повторно использовать клиентский код для заполнения моих пользовательских объектов Order и Client из 2 возвращенных таблиц данных.

Я надеялся сделать вот что:

Создайте динамическую строку SQL на клиенте, которая объединяет таблицу заказов с таблицей Items и фильтрует подходящие для каждой таблицы, как указано настраиваемым фильтром, созданным в приложении Winform fat-client. Сборка 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, недоступна из следующих двух статических SQL-запросов, и если я изменю статический SQL-запрос на динамический, никакие результаты не будут переданы обратно толстому клиенту.

На ум приходят 3 варианта, но я ищу лучший:

1) Первый SQL может быть выполнен путем выполнения динамически созданного SQL от клиента. Затем результаты могут быть переданы в виде таблицы в модифицированную версию вышеупомянутой хранимой процедуры. Я знаком с передачей табличных данных в формате XML. Если бы я сделал это, сохраненный процесс мог бы затем вставить данные во временную таблицу с помощью статического SQL, который, поскольку он был создан динамическим SQL, можно было бы запросить без проблем. (Я мог бы также изучить возможность передачи нового параметра типа таблицы вместо 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)

Это по-прежнему дает мне возможность повторно использовать мой клиентский код заполнения объектов, потому что заказы и элементы продолжают возвращаться в двух разных таблицах.

У меня есть ощущение, что у меня могут быть некоторые варианты использования типа данных Table в моем сохраненном процессе, но это также ново для меня, и я был бы признателен за небольшую ложку, кормящую его.

Если вы хотя бы так далеко отсканировали то, что я написал, я удивлюсь, но если это так, я не буду признателен за любые ваши мысли о том, как добиться этого наилучшим образом.