T-SQL Динамические таблицы SQL и Temp

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

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

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

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

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

Что я надеялся сделать, так это:

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

2) Я мог выполнить все запросы от клиента.

Первое будет выглядеть примерно так:

SELECT Items. * FROM Orders, Items WHERE Order.OrderId = Items.OrderId AND (динамический фильтр) SELECT Orders. * FROM Orders, Items WHERE Order.OrderId = Items.OrderId AND (динамический фильтр)

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

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

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

Ответы

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

create proc prTest2 @var varchar(100)
as
exec (@var)
go

create table #temp (id int)

exec prTest2 'insert #temp values(1)'

select * from #temp

  DECLARE @DynamicQuery NVarchar(MAX)
    Set @DynamicQuery='Select * into #temp from (select * from tablename) alias 
    select * from #temp
    drop table #temp'
    exec sp_executesql @DynamicQuery

IF OBJECT_ID('tempdb..##temp2') IS NULL
BEGIN
    exec ('create table ##temp2 (id int)
         insert ##temp2 values(1)')

    select * from ##temp2

END

IF OBJECT_ID('tempdb..##temp2') IS NOT NULL
DROP Table ##temp2

USE SandBox
GO

CREATE PROCEDURE usp_DynTest(@table_type AS VARCHAR(255))
AS 
BEGIN
    DECLARE @sql AS VARCHAR(MAX) = 'SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''' + @table_type + ''''
    EXEC (@sql)
END
GO

EXEC usp_DynTest 'BASE TABLE'
GO

EXEC usp_DynTest 'VIEW'
GO

DROP PROCEDURE usp_DynTest
GO

USE SandBox
GO

CREATE PROCEDURE usp_DynTest(@table_type AS VARCHAR(255))
AS 
BEGIN
    DECLARE @sql AS VARCHAR(MAX) = 'SELECT * INTO #temp FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''' + @table_type + '''; SELECT * FROM #temp;'
    EXEC (@sql)
END
GO

EXEC usp_DynTest 'BASE TABLE'
GO

EXEC usp_DynTest 'VIEW'
GO

DROP PROCEDURE usp_DynTest
GO