Похоже, что #temptables, созданные с помощью динамического SQL с помощью ВЫПОЛНИТЬ строкового метода, имеют другой объем и не могут быть сосланы "фиксированным" SQLs в той же хранимой процедуре. Однако я могу сослаться на временную таблицу, составленную динамическим SQL-оператором в подпоследовательности динамический SQL, но кажется, что хранимая процедура не возвращает результат запроса звонящему клиенту, если SQL не фиксируется.
Простые 2 сценария таблицы: у Меня есть 2 таблицы. Давайте назовем их Заказами и Объектами. Порядок имеет Первичный ключ OrderId, и Объекты имеет Первичный ключ ItemId. Объекты. OrderId является внешним ключом для идентификации родительского Порядка. Порядок может иметь 1 к n Объектам.
Я хочу смочь предоставить очень гибкий интерфейс типа "конструктора запросов" пользователю, чтобы позволить пользователю выбирать, какие Объекты он хочет видеть. Критерии фильтра могут быть основаны на полях от таблицы Items и/или от родительской таблицы Order. Если Объект удовлетворяет условию фильтра включая и условию на родительском Порядке, если Вы существуете, Объект должен быть возвратом в запросе, а также родительском Порядке.
Обычно, я предполагаю, большинство людей создало бы соединение между таблицей Item и родительскими таблицами Order. Я хотел бы выполнить 2 отдельных запроса вместо этого. Один для возврата всех Объектов квалификации и другого для возврата всех отличных родительских Заказов. Причина два, сворачиваются, и Вы можете или не можете согласиться.
Первая причина состоит в том, что я должен запросить все столбцы в родительской таблице Order и если бы я сделал единый запрос для соединения таблицы Orders с таблицей Items, то я был бы repoeating Информация для заказа многократно. С тех пор обычно существует большое количество объектов на Порядок, я хотел бы избежать этого, потому что это приведет к намного большему количеству данных, переданных толстому клиенту. Вместо этого как упомянуто, я хотел бы возвратить эти две таблицы индивидуально в наборе данных и использовать эти две таблицы в заполнить пользовательский Порядок и дочерние объекты клиента Объектов. (Я еще не знаю достаточно о LINQ или Платформе Объекта. Я создаю свои объекты вручную). Вторая причина я хотел бы возвратить две таблицы вместо, каждый - то, потому что у меня уже есть другая процедура, которая возвращает все Объекты для данного OrderId наряду с родительским Порядком, и я хотел бы использовать тот же подход с 2 таблицами так, чтобы я мог снова использовать клиентский код для заполнения моего пользовательского Порядка и Объектов клиента из этих 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 статических SQLs и если я изменяю статический SQLs на динамический, никакие результаты не пасуются назад к толстому клиенту.
3 вокруг пришедшего на ум, но я, ищут лучший:
1) Первый SQL мог быть выполнен путем выполнения динамично созданного SQL от клиента. Результаты могли затем быть переданы как таблица измененной версии вышеупомянутой хранимой процедуры. Я знаком с передающими данными таблицы как XML. Если бы я сделал это, то сохраненный proc мог бы затем вставить данные во временную таблицу с помощью статического 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 в моем сохраненном proc, но это также плохо мне знакомо, и я ценил бы определенную ложку, питающуюся той.
Если Вы даже просканировали настолько далеко, в каком я записал, я удивлен, но если так, я woul dappreciate любая из Ваших мыслей о том, как выполнить это лучше всего.
Сначала вам нужно создать свою таблицу, тогда она будет доступна в динамическом 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
Другими словами:
Вот полный пример:
CREATE PROC prTest2 @var VARCHAR(100)
AS
EXEC (@var)
GO
CREATE TABLE #temp (id INT)
EXEC prTest2 'insert #temp values(1)'
SELECT *
FROM #temp
Я настоятельно рекомендую вам прочитать http://www.sommarskog.se/arrays-in-sql-2005.html
Лично мне нравится подход передачи текстового списка с разделителями-запятыми, затем анализирует его с помощью функции текста в таблицу и присоединяется к ней. Подход с временной таблицей может работать, если вы сначала создадите ее в соединении. Но это немного сложнее.
Наборы результатов динамического 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