Страница 1 из 4
Динамическое формирование sql-запроса
Добавлено: 13 дек 2006, 16:27
Anderyt
Коллеги, помогите разобраться, плиз. у меня уже голова кипит, не могу найти решение, но уверен, что оно есть.
суть вот в чем.
в ЛоцияПДМ+ делаю отчет по всей базе, но с использованием запроса для предварительного отбора. отчет нужен для поиска определенных объектов по определенным ключевым словам, сейчас 4 слова, поиск по 4-м атрибутам. надо как то разобраться с той ситуацией, когда какой-либо из параметров поиска НЕ введен. сейчас я это делаю так:
Код: Выделить всё
select av1.object_id
from
lsdbo.attrib_value av1
,lsdbo.value_string vs1
where
av1.attrib_id = 100000191200011
and
av1.attrib_id = vs1.attrib_id and av1.value_id = vs1.id
and
vs1.value like case @obj2 when '' then '%' else '%'+@obj2+'%' end
такой вариант хоть и не обращает внимания на значения атрибута с приведенным ИД, но здорово тормозит процесс (если значение указать, ищет в несколько раз быстрее, при этом число строк в результате не сильно уменьшается) и требует простого существования этого атрибута у искомого объекта, что тоже не совсем нужно.
идея такая. я хочу как то изменить не параметры запроса (после оператора where, например), а вообще исключить этот блок из результирующего запроса. другими словами, на основе того, какие параметры поиска введены, склеить определенную строку запроса, в которой был бы поиск только указанных параметров в соответствующих атрибутах.
как это можно сделать в чистом Transact-SQL, чтобы потом это можно было вставить в условия предварительного отбора?
в Transact-SQL есть конструкции IF... ELSE и CASE ... WHEN... THEN... ELSE, но никак не могу их вставить в запрос так, чтобы хотя бы ошибок не было
Добавлено: 18 дек 2006, 09:25
Александр
ну это просто, смотри (мучаюсь в аналогичном направлении, не могу вернуть результат хп во временную таблицу, ну здесь не об этом)
вот такая хп
Код: Выделить всё
/*------------------------------------------------------
* получить таблицу значений атрибута сформированную
* на основании динамически сформированного запроса
*-----------------------------------------------------*/
ALTER Procedure lsdbo.user_Get_Attr_Value
( @ChAtrID nchar(18), --ID первого анализируемого атрибута искомого объекта
@TypeAtr nchar(15), --тип атрибута
@Operator nchar(2), --оператор сравнения
@ValueAtr nchar(15) --значение атрибута
)
As
Declare @SQLString NVARCHAR(1500) --строка для формирования SQL запроса
Declare @def NVARCHAR(15) --строка для значения атрибута
Set @def='@g NVARCHAR(15)'
Set @SQLString='
SELECT av.object_id
FROM lsdbo.attrib_value_view av,
lsdbo.value_'+@TypeAtr+' vv
WHERE av.value_id = vv.id AND
av.attrib_id = '+@ChAtrID+' AND
vv.attrib_id = '+@ChAtrID+' AND
vv.Value'+@Operator+'@g'
Exec sp_executesql @SQLString,@def,@g=@ValueAtr
примеры вызова
Код: Выделить всё
exec lsdbo.user_Get_Attr_Value '100000019000005','numeric_view','>','-1'
exec lsdbo.user_Get_Attr_Value '100000019000006','string_view','=','Менеджер'
смысл в следующем
системная функция sp_executesql выполняет сформированную строку запроса, т.е. рисуем запрос все изменяемые поля подставляем через переменные и работаем, ты можешь делать любые запросы формирующиеся на этапе выполнения (переменные заранее неизвестны).
Преимущества-гибкость
Недостатки - соответственно - скорость
(но кстати речь идет о только о микросекундах
)
Добавлено: 18 дек 2006, 09:38
Александр
или так
добавим еще одну переменную
@Addition varchar(50)
изменим SQL запрос на
Код: Выделить всё
Set @SQLString='
SELECT av.object_id
FROM lsdbo.attrib_value_view av,
lsdbo.value_'+@TypeAtr+' vv
WHERE av.value_id = vv.id AND
av.attrib_id = '+@ChAtrID+' AND
vv.attrib_id = '+@ChAtrID+' AND
vv.Value'+@Operator+'@g'+@Addition
Exec sp_executesql @SQLString,@def,@g=@ValueAtr
и вызываем
exec lsdbo.user_Get_Attr_Value '100000019000006','string_view','=','Менеджер','or vv.value='Сотрудник'
на синтаксис не смотри, просто для примера.
имея входные условия ты составляешь ЛЮБУЮ строку SQL запроса т.е. это просто текст, единственное-значение атрибута-его надо выделять в отдельный параметр
Добавлено: 18 дек 2006, 12:29
Александр
http://www.sql.ru/faq/faq_topic.aspx?fid=104
PS Все таки там много всего... Зря я на них наезжал
Добавлено: 18 дек 2006, 12:42
Anderyt
ну да, смотрел там
я правда не смог этот execute запихать в отчет в Лоции...
дело за теххелпом...
Добавлено: 18 дек 2006, 12:58
Александр
ну чем мог...
слушай, а проблема именно в вызове exec или в возвращаемом значении?
ведь результат динамического запроса можно вернуть только в существующую таблицу (=отчет) т.е. указать этот самый отчет для insert(ну или чего они там используют)?
PS хотя пока мало чего в этом понимаю
Добавлено: 18 дек 2006, 13:32
Anderyt
мне так кажется, что в самом по себе EXECUTE. так или иначе, в Теххелпе обещали рассмотреть вопрос, почему запрос с EXECUTE не вставляется в отчет...
Re: Динамическое формирование sql-запроса
Добавлено: 11 дек 2008, 17:10
gali
Буквально на днях билась с аналогичной задачей. Получила ответ теххэлпа:
Такого, скорее всего, никогда не будет. Эта конструкция не удовлетворяет синтаксису языка SQL.
Вы можете использовать в качестве аргументов значения, а не куски запросов. Кусок запроса вроде как не может быть аргументом.
Re: Динамическое формирование sql-запроса
Добавлено: 11 дек 2008, 17:19
Anderyt
gali писал(а):Такого, скорее всего, никогда не будет. Эта конструкция не удовлетворяет синтаксису языка SQL.
Вы можете использовать в качестве аргументов значения, а не куски запросов. Кусок запроса вроде как не может быть аргументом.
стоп стоп..
можно ведь рассматривать куски запроса не как аргументы, а как куски запроса, собственно
я думаю, если бы возникла ситуация, то можно было бы сваять выражение для строки, которая учитывала бы наличие тех или иных аргументов (или даже их значений). и потом эту строку подпихнуть в execute.
это разве не удовлетворяет синтаксису sql ? или вопрос уже не в этом (столько времени прошло, мог уже и потерять нить разговора)?
Re: Динамическое формирование sql-запроса
Добавлено: 11 дек 2008, 17:23
gali
Да, но execute НЕ ПРОШЛО! и я попыталась втолкнуть часть запроса в параметр. Понимаю, глупо, но так нужно!
Re: Динамическое формирование sql-запроса
Добавлено: 11 дек 2008, 23:13
Старик Крупский
Ну да. Если есть аргумент, который через двоеточие указывается, то он точно не может быть куском запроса. А вот слепить запрос из переменных - это вроде можно сделать. Когда-то давно пытался, но не помню чем это кончилось
Re: Динамическое формирование sql-запроса
Добавлено: 12 дек 2008, 06:37
Anderyt
gali писал(а):Да, но execute НЕ ПРОШЛО! и я попыталась втолкнуть часть запроса в параметр. Понимаю, глупо, но так нужно!
нужно не это
нужно, чтобы execute ПРОШЕЛ
Старик Крупский писал(а):А вот слепить запрос из переменных - это вроде можно сделать. Когда-то давно пытался, но не помню чем это кончилось
у меня закончилось тем же, чем и у gali... не прошло...
Re: Динамическое формирование sql-запроса
Добавлено: 12 дек 2008, 08:44
gali
Такая возможность: лепить запрос на ходу, реально нужна и, думаю, не только нам с Anderyt. Буквально на днях мне пришлось править штук 8 однотипных отчетов (добавлять атрибут, менять размеры и положение колонок. Это время и собственное здоровье (терпеть не могу механическую работу!) достойно лучшего применения. В результате я таки сделала один отчет, но получился монстр. Хорошо, что данных пока мало.
Старик Крупский, а не попытаться вспомнить?
Re: Динамическое формирование sql-запроса
Добавлено: 12 дек 2008, 09:50
Александр
Re: Динамическое формирование sql-запроса
Добавлено: 12 дек 2008, 10:14
Юрий
А вы не пробовали пользоваться временными таблицами?
Это реальный выход для увеличения функционала Лоции.
Принцип простой - Делаем свой кусок кода, в нем создаем временную таблицу и заполняем ее
нужными данными.
А следующей командой делаем селект из этой таблицы и результат используем в лоции.