Виртуальные таблицы в TSQL

← →
MsGuns © (2009-01-20 12:40) [0]

Вопрос возник из-за отсутствия в TSQL конструкции Select from <StoredProc>

Есть запрос на выборку Select... на который нужно наложить дополнительное условие (Where, Join, In..) на соответствие некоторому набору значений поля (полей), определяемому хранимкой. Однако вставить в запрос на выборку непосредственно ХП нельзя. Если ХП сохранит результат во временной таблице #Table, то она "не видна" извне. Создавать на клиенте (в большинстве случаев запрос формируется на клиенте) временную таблицу и передавать ее имя в ХП не желательно по ряду причин.
Как выйти из тупика ?

← →
Кщд (2009-01-20 13:35) [1]

"извне" - это откуда?
глобальные временные(##) не устраивают?

← →
Ega23 © (2009-01-20 13:40) [2]

> Если ХП сохранит результат во временной таблице #Table,
> то она "не видна" извне.

Это не так.
1. В MSSQL есть два вида временных таблиц: #ttt и ##tttt
У первого время жизни равно (или меньше) времени жизни коннекта. Причём данная таблица "видна" только из данного процесса, из другого будет создан другой экземпляр.
Второй вид - более "глобальный", его время жизни - до перезагрузки сервера.
2. Все временные таблицы создаются в рамках tempdb
3. При создании временной таблицы внутри ХП она будет автоматически убита при выходе из ХП.
С другой стороны, тебе никто не мешает создать таблицу ручками, передать её в качестве параметра в хп, поработать с ней, сделать потом ещё какую-то выборку, и после этого ручками убить.

← →
MsGuns © (2009-01-20 23:15) [3]

>Ega23 © (20.01.09 13:40) [2]
>> Если ХП сохранит результат во временной таблице #Table,
>> то она "не видна" извне.

>Это не так.

Как это "не так" сочетается с

>3. При создании временной таблицы внутри ХП она будет автоматически >убита при выходе из ХП.

?

← →
MsGuns © (2009-01-20 23:19) [4]

Т.е. ничего кроме

Create Table #ttt... Exec .. (вызов ХП с передачей имени таблицы параметром или "вшивка" в ХП имени #ttt) <ХП заполняет таблицу> Select ... left Join #ttt on ...

не получится ?

← →
Ega23 © (2009-01-21 10:15) [5]

> При создании временной таблицы внутри ХП она будет автоматически
> >убита при выходе из ХП.

Создай её до вызова ХП и бкдет тебе щщастье.

> Т.е. ничего кроме

Боюсь что да.
Если эстетически не нравится - ну оберни это дело другой хранимкой, или в этой же отдельную моду сделай. В TSQL с хранимками весьма большие вольности позволительны, в отличие от какого-нибудь FB или Postgres

← →
b z (2009-01-21 10:47) [6]

> из-за отсутствия в TSQL конструкции Select from <StoredProc>
Есть Select from <UserFunc>, - тоже не катит?

← →
b z (2009-01-21 10:55) [7]

> b z (21.01.09 10:47) [6]
+ Или вью организовать ... или то и то вместе :) вариант найти можно.

← →
sniknik © (2009-01-21 11:13) [8]

что обсуждается TSQL или MSSQL? в описании не указано хотя потом вроде ссылки/синтаксис MSSQL-я...

если второе то

> Т.е. ничего кроме
>
> Create Table #ttt...

> Exec .. (вызов ХП с передачей имени таблицы параметром или "вшивка" в ХП имени #ttt)

> <ХП заполняет таблицу>

> Select ... left Join #ttt on ...

> не получится ?

можно заменить на
Create Table #ttt...
insert #ttt exec "процедура возвращающая рекордсет"
Select ... left Join #ttt on ...
drop Table #ttt...
и не надо ничего передавать внутрь процедуры.

← →
MsGuns © (2009-01-21 11:45) [9]

>b z (21.01.09 10:47) [6]
>Есть Select from <UserFunc>, - тоже не катит?

В теле функции нельзя взывать процедуры

>b z (21.01.09 10:55) [7]
>+ Или вью организовать ... или то и то вместе :) вариант найти можно.

Дык в этом-то и вопрос заключается :)

>sniknik © (21.01.09 11:13) [8]
>что обсуждается TSQL или MSSQL? в описании не указано хотя потом вроде ссылки/синтаксис >MSSQL-я...
>если второе то

Да второе, конечно :)

>можно заменить на
> ...
>и не надо ничего передавать внутрь процедуры.

проблема в том, что та самая ХП достаточно навороченная (извлечение конструкторского состава изделия из графа констр.документации с кучей всяких нюансов: учет допустимых замен, расчет техотхода, определения маршрутов изготовления и т.д.) и не хотелось бы ее дублировать (именно ДУБЛИРОВАТЬ) аналогичной функцией.

шифр разузловываемого изделия, а также состав дополнительных (не относящихся к дереву состава) характеристик и условия выборки этих характеристик определяется на клиенте. Именно поэтому и возникла эта проблема. Т.к. кол-во клиентских приложений, юзающих дерево, весьма велико (и продолжает увеличиваться), хотелось бы придумать такой способ, который требовал бы минимальную ревизию проектов, вплоть до выноса текста самих запросов из IDE например на тот же сервер. В этом случае вообще проекты не надо было бы перекомпеллировать.
Но вот тут и вышла загвоздка.

Грубо говоря, есть некоторый select ... который возвращает то, что нужно задаче (приложению).
Этот селект приложение динамически дополняет условием Where исходя из текущих локальных требований пользователей. Все замечательно работает. Но работает ПО ВСЕЙ БАЗЕ. А надо чтоб только по изделию. Для чего и требуется "прикрутить" джоин к деталям и сборкам, относящимся к УКАЗАННОМУ изделию, а список этих самых деталей и сборок выдает та самая ХП.

← →
MsGuns © (2009-01-21 11:48) [10]

Говоря иначе нельзя чтобы какие-то таблицы создавал сам клиент. Он должен просто выполнять запрос и все !

Ну уже вывернулся весь, объясняя суть - лучше не умею :)

← →
Медвежонок Пятачок © (2009-01-21 12:01) [11]

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

← →
Ega23 © (2009-01-21 12:07) [12]

> Говоря иначе нельзя чтобы какие-то таблицы создавал сам
> клиент. Он должен просто выполнять запрос и все !

Напиши ещё одну ХП, суть которой сводится к:

1. Create table #xxx
2. insert into #xxx exec твоя_навороченная_процедура
3. Select * from #xxx со всеми джоинами.

← →
MsGuns © (2009-01-21 12:11) [13]

>Ega23 © (21.01.09 12:07) [12]

Не понял, каким боком в эту новую ХП я смогу встромить клиентский запрос с его (клиента) же списком where

← →
Медвежонок Пятачок © (2009-01-21 12:11) [14]

иными словами вначале клиентского запроса задекларить две переменные int и varchar
выполнить процедуру, получить ее xml данные внутрь варчара.
далее exec sp_xml_preparedocument @id out,@xml;

далее выполнить клиентский запрос, а джойны евонные выполнять к openxml(@id,"//......")

← →
Медвежонок Пятачок © (2009-01-21 12:18) [15]

declare @id int;
declare @xml varchar(max);
set @xml = "<root><item id="1"/><item id="2"/></root>";
exec sp_xml_preparedocument @id out,@xml;
select * from clients
where cl_id in (select id from openxml(@id,"/root/item",0) with (id int))
exec sp_xml_removedocument @id;

здесь вся разница с реальным случаем в том, что вызов хранимки заменен на простое присвоение set @xml = ....

← →
Ega23 © (2009-01-21 12:23) [16]

> Не понял, каким боком в эту новую ХП я смогу встромить клиентский
> запрос с его (клиента) же списком where

Через Exec. Динамический запрос, вобщем.

← →
b z (2009-01-21 13:41) [17]

> В теле функции нельзя взывать процедуры
А у вас там еще есть вложенные процедуры?

> Для чего и требуется "прикрутить" джоин к деталям и сборкам,
> относящимся к УКАЗАННОМУ изделию, а список этих самых деталей
> и сборок выдает та самая ХП.
хм, а зачем вам тут - "та самая ХП"? просто вставить необходимые условия "по месту" никак, т.е. все в одном?

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

недавно вот тоже делали автоматические расчеты на сервере, вышло около десятка процедур и несколько функций, тоже была засада в реализации элементов/алгоритмов комбинаторики ... решили с помощью clr (2005 сервер) + #temp таблицы, чуть-чуть подумали и все
а у вас пока выглядит как "дополняет условием Where исходя из текущих локальных требований пользователей", но делаете "странными" методами.
если таких параметров много и лень расписывать их всех, то дин. формирование запроса должно хватить, иначе и его наверное не надо
естественно, могу ошибаться

← →
Медвежонок Пятачок © (2009-01-21 14:03) [18]

так фикус-то у него в том, что динамические условия очень сложные и они уже реализованы в супер процедуре.

← →
b z (2009-01-21 14:16) [19]

> Медвежонок Пятачок © (21.01.09 14:03) [18]
Так я и говорю, что может отказаться от ее использования в пользу позрачности, скорости и т.д.
Ну в общем трудно судить не видя реалии, может и она не такая навороченная, может ее переделать, а может уже что-то из предложенного хватит (например sniknik © [8], оно отвергнуто? почему?) ... да мало ли.

← →
MsGuns © (2009-01-21 15:24) [20]

>Ega23 © (21.01.09 12:23) [16]
>Через Exec. Динамический запрос, вобщем.

Типа при помощи кувалды и какой-то матери :(

>b z (21.01.09 14:16) [19]
>Так я и говорю, что может отказаться от ее использования в пользу позрачности, скорости и т.д.
>Ну в общем трудно судить не видя реалии, может и она не такая навороченная, может ее >переделать

Я же вроде подробно написал о том, что не хочется ДУБЛИРОВАТЬ процедуру функцией.
Переделать ХП в ф-цию нельзя потому что она используется в бизнес логике и на клиентах скризь и рядом :)
Навороченность не от того, что говнопрограммисты писали, а оттого, что сам по себе расчет достаточно сложный - стандарты такие, обусловленные спецификой отрасли. Да и вылизана эта ХП так, что оптимизировать особо уже некуда. Она, кстати весьма реактивная :)

>а может уже что-то из предложенного хватит (например sniknik © [8], оно отвергнуто? >почему?) ... да мало ли.

см. [9]

← →
Ega23 © (2009-01-21 16:09) [21]

> Типа при помощи кувалды и какой-то матери :(

if exists (select * from sysobjects where id = object_id(N"[S_TestProc]") and OBJECTPROPERTY(id, N"IsProcedure") = 1) drop procedure [S_TestProc] GO CREATE PROCEDURE S_TestProc @Condition varchar(8000) = "1=1" As Set NoCount ON; Create Table #temp ( uid int identity (1,1), aName varchar(64) ); insert into #temp (aName) values ("A1"); insert into #temp (aName) values ("A2"); insert into #temp (aName) values ("A3"); insert into #temp (aName) values ("A4"); insert into #temp (aName) values ("A5"); insert into #temp (aName) values ("A6"); insert into #temp (aName) values ("A7"); insert into #temp (aName) values ("A8"); insert into #temp (aName) values ("A9"); insert into #temp (aName) values ("A10"); insert into #temp (aName) values ("A11"); exec ("Select * from #temp where " + @Condition); return(0); GO -- S_TestProc @Condition="uid>5";

← →
Медвежонок Пятачок © (2009-01-21 16:12) [22]

ганс, юзай мою методу, будет щастье.

← →
Ega23 © (2009-01-21 16:19) [23]

> ганс, юзай мою методу, будет щастье.

XML - гуано, ini-файлы рулят

← →
Медвежонок Пятачок © (2009-01-21 16:23) [24]

он позволяет обойтись без костылей

ini-файлы рулят

не. временные файлы интернета - вот кто рулит.

Спасибо. Похоже нет щастья на белом свете :(
Будем утяжелять клиента

вот что бывает когда не любишь xml

> см. [9]
не совсем понял написанное в [9] (читай совсем не понял), но увидел страшное слово "дублировать", а кто предлагал дублировать? там выполнение той самой процедуры, которая и так по твоим словам уже возвращает рекордсет... т.е. добавляется 3 строчки к sql пакету - создание временной таблицы, получение рекордсета из процедуры в нее, и удаление ее после, а до удаление вставляешь то что ты там хочешь с джойнами и вообще с чем хочешь.
и утяжелением добавление в ADODataSet 3х строк (или даже 30ти) я бы не назвал, так, мелкая добавка.

>sniknik © (21.01.09 21:29) [28]
>не совсем понял написанное в [9] (читай совсем не понял)

Значит [10] ;)

Ладно, всем спасибо

> Значит [10] ;)
тогда можешь пожертвовав производительностью получить рекордсет из процедуры через OPENROWSET подключая ее к своей же базе как к внешней.
это можно вставлять прям в запрос с джойном.

база pubs, там какаято процедура reptq1, при подключении к этой базе ->

SELECT a.* FROM OPENROWSET("SQLOLEDB","127.0.0.1";"sa";"123456", "exec pubs.dbo.reptq1") AS a

> вот что бывает когда не любишь xml

Да кто его любит? Максимум его преимуществ перед ini - это хранение иерархических настроек.

ЗЫ: CSV рулит :-)

> Я же вроде подробно написал о том, что не хочется ДУБЛИРОВАТЬ
> процедуру функцией.
> Переделать ХП в ф-цию нельзя потому что она используется
> в бизнес логике и на клиентах скризь и рядом :)
> Навороченность не от того, что говнопрограммисты писали,
> а оттого, что сам по себе расчет достаточно сложный - стандарты
> такие, обусловленные спецификой отрасли. Да и вылизана эта
> ХП так, что оптимизировать особо уже некуда. Она, кстати
> весьма реактивная :)

> > В теле функции нельзя взывать процедуры

Сделай наоборот: функционал - в функции. В процедуре, "используемой скризь и рядом" останется SELECT * FROM <ProcName>

А на клиентах получишь желаемое:
SELECT ...
FROM
<ProcName> p
JOIN ... ON p.xxx = ...
или
SELECT ...
FROM
<ProcName> p
WHERE p.xxc IN (...)

>Сделай наоборот: функционал - в функции. В процедуре, "используемой скризь и рядом" >останется SELECT * FROM <ProcName>

Мысль, конечно, хорошая, но ты знаешь, мне она тоже пришла в голову. Причем в самом начале.
Но эта "навороченная" ХП в свою очередь использует еще несколько других ХП. Тоже не маленьких..
В общем ситуация когда дачный сортир пытаются перестроить в особняк :)

>Медвежонок Пятачок © (21.01.09 16:12) [22]
>ганс, юзай мою методу, будет щастье.

Дадад, сразу как только с делфей пересяду на жабу, а мсскл поменяю на постгрес :)

Виртуальные таблицы в TSQL Найти похожие ветки