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

Question

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

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

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

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

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

То, что я надеялся сделать, было этим:

Создайте динамическую строку 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)

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

У меня есть чувство, что у меня могут быть некоторые варианты, использующие тип данных 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

Другими словами:

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

Answer 2

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

DECLARE @DynamicSQL NVARCHAR(MAX)
DECLARE @DynamicTable VARCHAR(255) = 'DynamicTempTable_' + CONVERT(VARCHAR(36), NEWID())
DECLARE @DynamicColumns NVARCHAR(MAX)

--Get "@DynamicColumns", example: SET @DynamicColumns = '[Column1], [Column2]'

SET @DynamicSQL = 'SELECT ' + @DynamicColumns + ' INTO [##' + @DynamicTable + ']' + 
     ' FROM [dbo].[TableXYZ]'

EXEC sp_executesql @DynamicSQL

SET @DynamicSQL = 'IF OBJECT_ID(''tempdb..##' + @DynamicTable + ''' , ''U'') IS NOT NULL ' + 
    ' BEGIN DROP TABLE [##' + @DynamicTable + '] END'

EXEC sp_executesql @DynamicSQL

Конечно, не лучшее решение, но мне кажется, что это работает.

Answer 3

1-й метод - заключить несколько операторов в один и тот же динамический вызов SQL:

DECLARE @DynamicQuery NVARCHAR(MAX)

SET @DynamicQuery = 'Select * into #temp from (select * from tablename) alias 
select * from #temp
drop table #temp'

EXEC sp_executesql @DynamicQuery

2-й метод - использовать глобальную временную таблицу:
(Осторожно, вам нужно позаботиться о глобальной переменной.)

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

    SELECT *
    FROM ##temp2
END

Не забудьте удалить объект ##temp2 вручную, как только вы закончите с ним:

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

Примечание: не используйте этот метод 2, если вы не знаете полную структуру базы данных.

Answer 4

Я настоятельно рекомендую вам ознакомиться с http://www.sommarskog.se/arrays-in-sql-2005.html

Лично мне нравится подход передачи текстового списка через запятую, затем разбора его с функцией text to table и присоединения к нему. Подход временной таблицы может работать, если вы сначала создадите его в соединении. Но это немного грязно.

Answer 5

Наборы результатов из динамического SQL возвращаются клиенту. Я сделал это довольно много.

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

Я думаю, пытаясь заставить вашу временную таблицу работать, вы, вероятно, запутали некоторые вещи, потому что вы определенно можете получить данные от SP, который выполняет динамический SQL:

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