Динамическое формирование sql-запроса

Обсуждение технических вопросов работы с системами управления базами данных (СУБД), работе с языком SQL и скриптовыми языками.
Аватара пользователя
Anderyt
Активный участник
Сообщения: 777
Зарегистрирован: 15 июл 2004, 13:15
Используемое ПО: Lotsia PDM PLUS
Откуда: Тюмень
Контактная информация:

Динамическое формирование sql-запроса

Сообщение 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, но никак не могу их вставить в запрос так, чтобы хотя бы ошибок не было :-(
лучше день потерять, потом за пять минут долететь!
Аватара пользователя
Александр
Активный участник
Сообщения: 1652
Зарегистрирован: 24 авг 2006, 08:06
Используемое ПО: Lotsia PDM PLUS
Откуда: 55.745578,37.665825

Сообщение Александр »

ну это просто, смотри (мучаюсь в аналогичном направлении, не могу вернуть результат хп во временную таблицу, ну здесь не об этом)
вот такая хп

Код: Выделить всё

/*------------------------------------------------------
 * получить таблицу значений атрибута сформированную
 * на основании динамически сформированного запроса 
 *-----------------------------------------------------*/
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 выполняет сформированную строку запроса, т.е. рисуем запрос все изменяемые поля подставляем через переменные и работаем, ты можешь делать любые запросы формирующиеся на этапе выполнения (переменные заранее неизвестны).
Преимущества-гибкость
Недостатки - соответственно - скорость :wink: (но кстати речь идет о только о микросекундах :wink: )

Софт - RicCRM<<LotsiaPDM(4.40)<<MsSQL(5/8)
Уровень администрирования - Альтернативный

Аватара пользователя
Александр
Активный участник
Сообщения: 1652
Зарегистрирован: 24 авг 2006, 08:06
Используемое ПО: Lotsia PDM PLUS
Откуда: 55.745578,37.665825

Сообщение Александр »

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

Софт - RicCRM<<LotsiaPDM(4.40)<<MsSQL(5/8)
Уровень администрирования - Альтернативный

Аватара пользователя
Александр
Активный участник
Сообщения: 1652
Зарегистрирован: 24 авг 2006, 08:06
Используемое ПО: Lotsia PDM PLUS
Откуда: 55.745578,37.665825

Сообщение Александр »

http://www.sql.ru/faq/faq_topic.aspx?fid=104

PS Все таки там много всего... Зря я на них наезжал :wink:

Софт - RicCRM<<LotsiaPDM(4.40)<<MsSQL(5/8)
Уровень администрирования - Альтернативный

Аватара пользователя
Anderyt
Активный участник
Сообщения: 777
Зарегистрирован: 15 июл 2004, 13:15
Используемое ПО: Lotsia PDM PLUS
Откуда: Тюмень
Контактная информация:

Сообщение Anderyt »

ну да, смотрел там :-)
я правда не смог этот execute запихать в отчет в Лоции... :-(
дело за теххелпом...
лучше день потерять, потом за пять минут долететь!
Аватара пользователя
Александр
Активный участник
Сообщения: 1652
Зарегистрирован: 24 авг 2006, 08:06
Используемое ПО: Lotsia PDM PLUS
Откуда: 55.745578,37.665825

Сообщение Александр »

ну чем мог...
слушай, а проблема именно в вызове exec или в возвращаемом значении?
ведь результат динамического запроса можно вернуть только в существующую таблицу (=отчет) т.е. указать этот самый отчет для insert(ну или чего они там используют)?

PS хотя пока мало чего в этом понимаю

Софт - RicCRM<<LotsiaPDM(4.40)<<MsSQL(5/8)
Уровень администрирования - Альтернативный

Аватара пользователя
Anderyt
Активный участник
Сообщения: 777
Зарегистрирован: 15 июл 2004, 13:15
Используемое ПО: Lotsia PDM PLUS
Откуда: Тюмень
Контактная информация:

Сообщение Anderyt »

мне так кажется, что в самом по себе EXECUTE. так или иначе, в Теххелпе обещали рассмотреть вопрос, почему запрос с EXECUTE не вставляется в отчет...
лучше день потерять, потом за пять минут долететь!
gali
Активный участник
Сообщения: 285
Зарегистрирован: 27 мар 2007, 07:43

Re: Динамическое формирование sql-запроса

Сообщение gali »

Буквально на днях билась с аналогичной задачей. Получила ответ теххэлпа:

Такого, скорее всего, никогда не будет. Эта конструкция не удовлетворяет синтаксису языка SQL.
Вы можете использовать в качестве аргументов значения, а не куски запросов. Кусок запроса вроде как не может быть аргументом.
Аватара пользователя
Anderyt
Активный участник
Сообщения: 777
Зарегистрирован: 15 июл 2004, 13:15
Используемое ПО: Lotsia PDM PLUS
Откуда: Тюмень
Контактная информация:

Re: Динамическое формирование sql-запроса

Сообщение Anderyt »

gali писал(а):Такого, скорее всего, никогда не будет. Эта конструкция не удовлетворяет синтаксису языка SQL.
Вы можете использовать в качестве аргументов значения, а не куски запросов. Кусок запроса вроде как не может быть аргументом.
стоп стоп..
можно ведь рассматривать куски запроса не как аргументы, а как куски запроса, собственно :-)
я думаю, если бы возникла ситуация, то можно было бы сваять выражение для строки, которая учитывала бы наличие тех или иных аргументов (или даже их значений). и потом эту строку подпихнуть в execute.
это разве не удовлетворяет синтаксису sql ? или вопрос уже не в этом (столько времени прошло, мог уже и потерять нить разговора)?
лучше день потерять, потом за пять минут долететь!
gali
Активный участник
Сообщения: 285
Зарегистрирован: 27 мар 2007, 07:43

Re: Динамическое формирование sql-запроса

Сообщение gali »

Да, но execute НЕ ПРОШЛО! и я попыталась втолкнуть часть запроса в параметр. Понимаю, глупо, но так нужно!
Аватара пользователя
Старик Крупский
Активный участник
Сообщения: 803
Зарегистрирован: 27 июл 2006, 22:17
Откуда: Москва

Re: Динамическое формирование sql-запроса

Сообщение Старик Крупский »

Ну да. Если есть аргумент, который через двоеточие указывается, то он точно не может быть куском запроса. А вот слепить запрос из переменных - это вроде можно сделать. Когда-то давно пытался, но не помню чем это кончилось :(
"Лучше меньше, да лучше" (C)
Аватара пользователя
Anderyt
Активный участник
Сообщения: 777
Зарегистрирован: 15 июл 2004, 13:15
Используемое ПО: Lotsia PDM PLUS
Откуда: Тюмень
Контактная информация:

Re: Динамическое формирование sql-запроса

Сообщение Anderyt »

gali писал(а):Да, но execute НЕ ПРОШЛО! и я попыталась втолкнуть часть запроса в параметр. Понимаю, глупо, но так нужно!
нужно не это ;-) нужно, чтобы execute ПРОШЕЛ ;-)
Старик Крупский писал(а):А вот слепить запрос из переменных - это вроде можно сделать. Когда-то давно пытался, но не помню чем это кончилось
у меня закончилось тем же, чем и у gali... не прошло...
лучше день потерять, потом за пять минут долететь!
gali
Активный участник
Сообщения: 285
Зарегистрирован: 27 мар 2007, 07:43

Re: Динамическое формирование sql-запроса

Сообщение gali »

Такая возможность: лепить запрос на ходу, реально нужна и, думаю, не только нам с Anderyt. Буквально на днях мне пришлось править штук 8 однотипных отчетов (добавлять атрибут, менять размеры и положение колонок. Это время и собственное здоровье (терпеть не могу механическую работу!) достойно лучшего применения. В результате я таки сделала один отчет, но получился монстр. Хорошо, что данных пока мало.
Старик Крупский, а не попытаться вспомнить?
Аватара пользователя
Александр
Активный участник
Сообщения: 1652
Зарегистрирован: 24 авг 2006, 08:06
Используемое ПО: Lotsia PDM PLUS
Откуда: 55.745578,37.665825

Re: Динамическое формирование sql-запроса

Сообщение Александр »

мы ни разу не сталкивались с необходимостью лепить запрос на ходу :wink: :wink: :wink:

может посмотреть на структуру данных? и понять что эта задача либо не нужна вообще либо решается совершенно по другому :wink: :wink: :wink: :wink:

Софт - RicCRM<<LotsiaPDM(4.40)<<MsSQL(5/8)
Уровень администрирования - Альтернативный

Юрий
Активный участник
Сообщения: 239
Зарегистрирован: 13 янв 2005, 14:30
Используемое ПО: Lotsia PDM PLUS LT
Откуда: Украина, Донецк
Контактная информация:

Re: Динамическое формирование sql-запроса

Сообщение Юрий »

А вы не пробовали пользоваться временными таблицами?
Это реальный выход для увеличения функционала Лоции.

Принцип простой - Делаем свой кусок кода, в нем создаем временную таблицу и заполняем ее
нужными данными.
А следующей командой делаем селект из этой таблицы и результат используем в лоции.
Ответить