Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Можно ли подключать/отключать условия в запросе не меняя текст?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
Можно ли подключать/отключать условия в запросе не меняя текст?
т.е. если текст запроса
select * from t
where
f1=@p1 and f2=@p2

и при одних обстоятельствах в условии должна выполняться проверка f1 и f2, а при других, например, только f1, но чтобы не приходилось менять сами условия в тексте, а контролировать эту необходимость через значение переменной
типа (очень образно, конечно):
select * from t
where
(if @p1 is not null then выполняем проверку на соответствие f1=@p1)
and
(if @p2 is not null then выполняем проверку на соответствие f2=@p2)

существуют ли такие приемы?
6 сен 12, 11:36    [13122634]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли подключать/отключать условия в запросе не меняя текст?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
тоже образно типа такого:

select * from t
where
(@p1 is null or (@p1 is not null and f1=@p1))
and
(@p2 is null or (@p2 is not null and f2=@p2))
6 сен 12, 11:45    [13122708]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли подключать/отключать условия в запросе не меняя текст?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
Winnipuh,

спасибо!)
6 сен 12, 11:55    [13122795]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли подключать/отключать условия в запросе не меняя текст?  [new]
Stilet
Member

Откуда: УФА, БАШКОРТОСТАН
Сообщений: 411
select * from t
where
f1=case when @p1 is null then f1 else @p1 end
and
f2=case when @p2 is null then f2 else @p2 end
7 сен 12, 14:49    [13130336]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли подключать/отключать условия в запросе не меняя текст?  [new]
Anddros
Member

Откуда:
Сообщений: 1077
Stilet
select * from t
where
f1=case when @p1 is null then f1 else @p1 end
and
f2=case when @p2 is null then f2 else @p2 end

А если в f1 или f2 null лежит? :)
7 сен 12, 15:01    [13130434]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли подключать/отключать условия в запросе не меняя текст?  [new]
gang
Member

Откуда:
Сообщений: 1394
TJ001,

Коллега Winnipuh в общем прав. Чаще даже пишут короче myField=@p1 or @p1 is null - это наиболее надежный и простой с точки зрения сложности кода вариант, но он крайне неэффективен с точки зрения производительности, т.к. оптимизатор при такой конструкции игнорирует индексы по myField. Вариант от Stilet потенциально более производительный. Т.е пишется поле, оператор сравнения и некие вычисляемые значения, которые при отсутствии параметра эквивалентны отсутствию фильтра. Для int например будет примерно так
myIntField berween isnull(@p1, -2147483648) and isnull(@p1, 2147483647)

тут правда уже нужно точно знать структуру, а иногда и физический смысл данных чтобы написать адекватное условие.
7 сен 12, 16:03    [13131046]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли подключать/отключать условия в запросе не меняя текст?  [new]
iap
Member

Откуда: Москва
Сообщений: 47047
Winnipuh
тоже образно типа такого:

select * from t
where
(@p1 is null or (@p1 is not null and f1=@p1))
and
(@p2 is null or (@p2 is not null and f2=@p2))
Если f1=@p1, то я гарантирую, что @p1 обязательно is not null
7 сен 12, 16:09    [13131093]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли подключать/отключать условия в запросе не меняя текст?  [new]
case when
Guest
select * from t
where
case when @p1 is null then 1 else f1 end = case when @p1 is null then 1 else @p1 end
and
case when @p2 is null then 1 else f2 end = case when @p2 is null then 1 else @p2 end
7 сен 12, 16:28    [13131282]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли подключать/отключать условия в запросе не меняя текст?  [new]
TJ001
Member

Откуда: от верблюда
Сообщений: 428
iap,

я это гарантирую© )))

да избыточное условие, но на мысль натолкнуло

спасибо всем за участие!
7 сен 12, 17:49    [13131902]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли подключать/отключать условия в запросе не меняя текст?  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
gang
Чаще даже пишут короче myField=@p1 or @p1 is null - это наиболее надежный и простой с точки зрения сложности кода вариант, но он крайне неэффективен с точки зрения производительности, т.к. оптимизатор при такой конструкции игнорирует индексы по myField.


Ничего он не игнорирует, скорее может компилирует и запоминает план, option recompile - спасает.

if exists ( select  *
                from    sys.tables t
                        inner join sys.schemas s on t.schema_id = s.schema_id
                where   s.name = 'dbo'
                        and t.name = 'Test' ) 
    begin					
        drop table dbo.Test		    
    end
go
    
create table dbo.Test ( 
	id int identity ( 1, 1 ) primary key clustered
  , f1 int null
  , f2 int null
)
go

;
with    cte ( id )
          as ( select   1
               union all
               select   id + 1
               from     cte
               where    id < 2000000
             )
insert into dbo.Test ( f1, f2 )
    select  case when id % 6544 = 0 then null else id % 6544 end
		  , case when id % 1235 = 0 then null else id % 1235 end
    from    cte
option  ( maxrecursion 0 )  
go

--drop index dbo.Test.IDX_tmp_test_f1_f2
create index IDX_tmp_test_f1_f2 on dbo.Test ( f1, f2 )
go

--drop index dbo.Test.IDX_tmp_test_f2_f1
create index IDX_tmp_test_f2_f1 on dbo.Test ( f2, f1 )
go

set statistics io on;
go

--test 2
declare @p1 int = null
declare @p2 int = 6

select  *
from    dbo.Test 
where   ( f1 = @p1
          or @p1 is null
        )
        and ( f2 = @p2
              or @p2 is null
            )
option (recompile)

go

-- test 2
declare @p1 int = 21
declare @p2 int = 6

select  *
from    dbo.Test
where   ( f1 = @p1
          or @p1 is null
        )
        and ( f2 = @p2
              or @p2 is null
            )
option (recompile)


go

-- test 3
declare @p1 int = 21
declare @p2 int = null

select  *
from    dbo.Test
where   ( f1 = @p1
          or @p1 is null
        )
        and ( f2 = @p2
              or @p2 is null
            )
option (recompile)
7 сен 12, 18:15    [13132064]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли подключать/отключать условия в запросе не меняя текст?  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
case when
select * from t
where
case when @p1 is null then 1 else f1 end = case when @p1 is null then 1 else @p1 end
and
case when @p2 is null then 1 else f2 end = case when @p2 is null then 1 else @p2 end


Вот этот вариан вообще пипец посмотрите на IO
declare @p1 int = 21
declare @p2 int = 6

select * from dbo.Test
where
case when @p1 is null then 1 else f1 end = case when @p1 is null then 1 else @p1 end
and
case when @p2 is null then 1 else f2 end = case when @p2 is null then 1 else @p2 end



(2 row(s) affected)
Table 'Test'. Scan count 3, logical reads 4525, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)
7 сен 12, 18:22    [13132093]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли подключать/отключать условия в запросе не меняя текст?  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
Владимир Затуливетер
(2 row(s) affected)
Table 'Test'. Scan count 3, logical reads 4525, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


Беру слова обратно, если выставить option (recompile) все работает нормально.
7 сен 12, 18:27    [13132119]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли подключать/отключать условия в запросе не меняя текст?  [new]
Serg_77m
Member

Откуда: Донецк
Сообщений: 237
TJ001,

select * from t where @p1 is not null and @p2 is not null and f1=@p1 and f2=@p2
union all
select * from t where @p1 is null and @p2 is not null and f2=@p2
union all
select * from t where @p1 is not null and @p2 is null and f1=@p1
union all
select * from t where @p1 is null and @p2 is null

В зависимости от того null/not null в переменных @p1,@p2, выбирается одна из веток union all, причём могут быть задействованы индексы. Можно и хинт написать, если оптимизатор сам не догадается нужный индекс подключить.
7 сен 12, 19:22    [13132300]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли подключать/отключать условия в запросе не меняя текст?  [new]
CyberWeber001
Member

Откуда: Баку,Азербайджан
Сообщений: 13
Можно формировать стороку запроса в виде текста и в зависимости от условия подключать нужное а потом суммарное запускать с помощью sp_executesql...
9 сен 12, 15:00    [13136171]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли подключать/отключать условия в запросе не меняя текст?  [new]
CyberWeber001
Member

Откуда: Баку,Азербайджан
Сообщений: 13
Ну типа так

Declare @Zapros_obs as nvarchar(500)
Set @Zapros_obs =' select * from t where'
Declare @Zapros_usloviye as nvarchar(100)
if @p1 is not null
Set @Zapros_usloviye=' f1=@p1'
else if @p2 is not null
Set @Zapros_usloviye=' f2=@p1'

Set @Zapros_obs=@Zapros_obs+' @Zapros_usloviye'
Exec sp_executesql @Zapros_obs
9 сен 12, 15:05    [13136183]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить