Microsoft SQL Server
Transact-SQL

Переменное число критериев отбора в запросе

Опубликовано: 28 сен 02
Рейтинг:

Автор: Cat2
Прислал: Cat2

/*
Необходимо написать процедуру для выборки данных, в которой некоторые критерии отбора не принимались бы во внимание, если пользователь не ввел для них значения.

Приведенная ниже процедура делает отбор по следующим критериям:
pubdate больше или равно @pubdateStart;
pubdate меньше или равно @pubdateEnd;
в title содержится строка @title;
price больше или равно @price;
ytd_sales равно @ytd_sales;

Для сравнения дат используются минимально и максимально возможные значения DateTime.
Предполагается, что поле Price не может быть меньше нуля. В реальной программе это может быть и другая константа. В любом случае можно использовать минимальное или максимальное значение для выбранного типа данных.
*/

use pubs
go
create procedure test 
@pubdateStart datetime=null, @pubdateEnd datetime=null,
@title varchar(80)=null,
@price money=null,
@ytd_sales int=null
as 
select pubdate,title,price,ytd_sales from titles
where pubdate between isnull(@pubdateStart,'17530101') and isnull(@pubdateEnd,'99991231')
and title like '%'+isnull(@title,'')+'%'
and price>=isnull(@price,0)
and ytd_sales=isnull(@ytd_sales,ytd_sales)
order by pubdate,title,price,ytd_sales
go
exec test 

exec test '19910610'
exec test null,'19910630'
exec test '19910610','19910630'
exec test '19910610','19910630','t'
exec test '19910610','19910630','t',3
exec test '19910610','19910630','t',3,3336
exec test null,null,null,null,3336

drop procedure test 
--Cat2

Комментарии


  • (price >= @price or @price is null)
    Вот так действительно быстрее в ряде случаев.
    Так что, фак то надо бы доделать.

  • А теперь такой вопрос. Как сделать то же самое, но с where in?
    Что-то вроде:
    create procedure test
    @titleList varchar(80)=null,
    as
    select title from titles
    where title in(@titleList ???? ) -- если @titleList is null, то нужно не учитывать это условие
    order by pubdate,title,price,ytd_sales

  • дин запросы спокойно идут под 2005, с правами косяков нет, главное "WITH EXECUTE AS OWNER" не забыть указать. ну и дыр для эксплойтов не делать :)
    а так - да - тема стремная, крайне странно, почему господа из MS никак это не решают, с константами-то все в шоколаде давно, опцию сделать и щасте всем

  • >>а совсем хорошо -
    >>..........................
    >>and title like '%'+isnull(@title,'')+'%' or title Is Null

    нет, немного не так. Вот так надо:
    and isnull(title,'') like '%'+isnull(@title,'')+'%'

    Теперь у меня все работает как надо!!!!

  • а совсем хорошо -
    ..........................
    and title like '%'+isnull(@title,'')+'%' or title Is Null

  • >> А может использовать Or
    >> типа (price >= @price or price is null)

    Нет, лучше не так, а вот как:
    bla-bla-bla AND
    (price >= @price or @price is null) AND
    bla-bla-bla

  • А может использовать Or

    типа (price >= @price or price is null)

  • при использовании дин.запросов у пользователя должны быть права на таблицу. Если права дать нельзя по каким либо причинам, то остается только этот вариант. лично я использую этот вариант. если вдруг тормозит, то определяю какой критерий самый медленный и выношу его в отдельный запрос с помощью if. но в большинстве случаев проблем не бывает

  • Вместо and ytd_sales=isnull(@ytd_sales,ytd_sales)
    правильнее будет ytd_sales between isnull(@ytd_sales,-2147483648) and isnull(@ytd_sales,2147483647)

    Так хоть индексы можно будет использовать.

  • Вместо and ytd_sales=isnull(@ytd_sales,ytd_sales)
    правильнее будет ytd_sales between isnull(@ytd_sales,-2147483648) and isnull(@ytd_sales,2147483647)

    Так хоть индексы можно будет использовать.

  • Я над такой штукой думала.
    Про даты понятно..
    Но у меня там были и ключи - int.
    Вот не придумала, что можно написать не какое-то значение, а условие - ">0".
    Меня это и смущало...
    Но у мня большая таблица.
    Не будет ли запрос из-за сплошных isnull - тормозить??

  • Подходит только если таблица маленькая, или есть постоянные критерии отбора, обеспечивающие высокую избирательность. Потому что при фильтрации типа ytd_sales=isnull(@ytd_sales,ytd_sales) индекс по ytd_sales использоваться НЕ БУДЕТ.

  • о! то что нужно!!!

  • +1 в пользу динамического запроса

  • Мне так кажется при таком подходе в общем оптимизатор всё равно предпримет фильтрацию результатов в то время когда запрос в ней не нуждается то же самое касается и использования индексов очевидно что для запросов такого характера не применимы иные индексы кроме кластерных(первичных)

  • а я бы не заморачивался и создавал бы динамический запрос в зависимости от заданных параметров



Необходимо войти на сайт, чтобы оставлять комментарии

Раздел FAQ: Microsoft SQL Server / Transact-SQL / Переменное число критериев отбора в запросе