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

Откуда:
Сообщений: 182
Предположим, что в некой таблице есть некое поле (это из разряда фантастики, но все же предположим).
Как написать параметризуемый запрос, который позволит как искать данные по точному вхождению этих полей, так и по условию null/not null ?

where Value=@Param1 не подходит.
13 сен 18, 14:03    [21673550]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null / is not null ?  [new]
WaspNewCore
Member

Откуда:
Сообщений: 182
а, ну и чтобы было достаточно производительным при поиске - читай индексы.
13 сен 18, 14:13    [21673577]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null  [new]
invm
Member

Откуда: Москва
Сообщений: 9350
WaspNewCore
читай индексы.
Тогда так
select
 ...
from
 ...
where
 @Param is null and
 Value is null

union all

select
 ...
from
 ...
where
 @Param is not null and
 Value = @Param
Или так
select
 ...
from
 ...
where
 (@Param is null and Value = is null) or
 (@Param is not null and Value = @Param)
option
 (recompile)
Или строить динамический запрос.
13 сен 18, 14:33    [21673626]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null / is not null ?  [new]
WaspNewCore
Member

Откуда:
Сообщений: 182
Вариант с union all что-то совсем не нравится.
Во первых. параметр такой (который нужно проверять на null или реальное значение) может быть не один. А значит, что количество запросов будет степенью двойки от числа этих параметров.....
Во вторых. Кроме этого параметра есть же и другие, принимающие участие. А это значит, что база будет вынуждена прогнать два сложных запроса и потом еще объединить результаты через distinct.

Во втором случае тоже не очень ясно. У нас имеется 3 ситуации: заполнено, не заполнено, конкретное значение. В примере обрабатывается только is null + точное знаничение. А вот как вписать в это третий вариант с not null ?

Динамический запрос тоже не очень нравится. Хотелось как раз уйти от этого. Динамический запрос то я могу и так построить хоть весь, с рекомпилом. Если придется часть строить динамически, а часть параметрами, то проще наверное уж строить динамически весь (ну имеется ввиду, что все равно используя параметры, но в динамически сгенеренном запросе)
13 сен 18, 14:45    [21673652]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null / is not null ?  [new]
256k
Member

Откуда: с.Торчилово, Псковская обл.
Сообщений: 437
invm
WaspNewCore
читай индексы.
Тогда так
select
 ...
from
 ...
where
 @Param is null and
 Value is null

union all

select
 ...
from
 ...
where
 @Param is not null and
 Value = @Param
Или так
select
 ...
from
 ...
where
 (@Param is null and Value is null) or
 (@Param is not null and Value = @Param)
option
 (recompile)
Или строить динамический запрос.


Value = is null исправил
13 сен 18, 14:48    [21673660]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null  [new]
invm
Member

Откуда: Москва
Сообщений: 9350
WaspNewCore
Во втором случае тоже не очень ясно. У нас имеется 3 ситуации: заполнено, не заполнено, конкретное значение.
Чем, с точки зрения значения параметра, отличается "заполнено" от "конкретное значение"?
256k
Value = is null исправил
Спасибо.
13 сен 18, 15:17    [21673714]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20538
WaspNewCore
параметризуемый запрос, который позволит как искать данные по точному вхождению этих полей, так и по условию null/not null ?

where Value=@Param1 не подходит
Ну то, что Value - это поле, вроде понятно. Что @Param - параметр, тоже понятно. Что в @Param передаётся значение для поиска - и это понятно. А что будет передаваться, чтобы искать IS NULL (вероятно, NULL), и особенно интересно, что будет передаваться, если надо искать NOT NULL?
13 сен 18, 15:18    [21673721]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null / is not null ?  [new]
WaspNewCore
Member

Откуда:
Сообщений: 182
Akina
WaspNewCore
параметризуемый запрос, который позволит как искать данные по точному вхождению этих полей, так и по условию null/not null ?

where Value=@Param1 не подходит
Ну то, что Value - это поле, вроде понятно. Что @Param - параметр, тоже понятно. Что в @Param передаётся значение для поиска - и это понятно. А что будет передаваться, чтобы искать IS NULL (вероятно, NULL), и особенно интересно, что будет передаваться, если надо искать NOT NULL?


У меня тоже такой вопрос. Думал о разных вариантов - как например передача еще доп параметра, указывающего как трактовать. Но поэтому и спросил тут.
13 сен 18, 15:23    [21673737]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
только так думаю
create proc dbo.GetData
    @Param1 int = null
  , @Param1Mode tinyint
  , @Param2 int = null
  , @Param2Mode tinyint 
as 
/* param mode:
    1 - null 
    2 - not null
    3 - value
*/
    select *
    from dbo.Table1 t
    where ( 
               @Param1Mode1 = 1 and Value1 is null 
            or @Param1Mode1 = 2 and Value1 is not null
            or @Param1Mode1 = 3 and Value1 = @Param1
        )
        and ( 
               @Param1Mode2 = 1 and Value2 is null 
            or @Param1Mode2 = 2 and Value2 is not null
            or @Param1Mode2 = 3 and Value2 = @Param2
        )
        -- ...
    option ( recompile );

go
13 сен 18, 15:24    [21673741]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null / is not null ?  [new]
WaspNewCore
Member

Откуда:
Сообщений: 182
invm
WaspNewCore
Во втором случае тоже не очень ясно. У нас имеется 3 ситуации: заполнено, не заполнено, конкретное значение.
Чем, с точки зрения значения параметра, отличается "заполнено" от "конкретное значение"?


Ну если мы хотим найти тех юзеров, у которых это поле в принципе заполнено, не важно чем. Либо если ищем тех чье значение нам точно известно и их хотим найти. Это разные ситуации поиска. Найти всех кто хотя бы раз "что-то там, что привело к заполнению поля" и тех, кто "что-то там ровно 3 раза".
13 сен 18, 15:26    [21673748]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20538
WaspNewCore
Думал о разных вариантов - как например передача еще доп параметра, указывающего как трактовать.
Теоретически у Value обязан быть некий смысл. А в его рамках обязано существовать некое невозможное значение. Именно такое значение можно использовать как признак "дай мне NOT NULL". Тогда всё превратится в некое (с точностью до синтаксиса)
WHERE CASE WHEN @Param IS NULL         THEN Value IS NULL
           WHEN @Param = @NotNullConst THEN Value IS NOT NULL
                                       ELSE Value = @Param
           END
Для MS SQL видимо нужно обернуть значения-проверки в IF().
13 сен 18, 15:31    [21673765]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null  [new]
iap
Member

Откуда: Москва
Сообщений: 46983
Akina
WaspNewCore
параметризуемый запрос, который позволит как искать данные по точному вхождению этих полей, так и по условию null/not null ?

where Value=@Param1 не подходит
Ну то, что Value - это поле, вроде понятно. Что @Param - параметр, тоже понятно. Что в @Param передаётся значение для поиска - и это понятно. А что будет передаваться, чтобы искать IS NULL (вероятно, NULL), и особенно интересно, что будет передаваться, если надо искать NOT NULL?
Это же очевидно.
Параметр должен быть строковым и содержать 'NULL', 'NOT NULL' или конкретные значения.
13 сен 18, 15:33    [21673769]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
iap
Параметр должен быть строковым

Только может для строковых параметров стработает, и то с натяжкой, может кто-то захочет искать NULL?
13 сен 18, 15:34    [21673779]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null  [new]
iap
Member

Откуда: Москва
Сообщений: 46983
Владимир Затуливетер
iap
Параметр должен быть строковым

Только может для строковых параметров стработает, и то с натяжкой, может кто-то захочет искать NULL?
Ну и передавать 'NULL'.
Можно и дальше извращаться. Например, передавать в параметре строку со списком значений (обрабатывать LIKEом, например).
Или вообще передавать свои специальные слова. Например, 'ANY' для любого значения. И т.д., как говорится...
13 сен 18, 15:39    [21673789]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null  [new]
Владимир Затуливетер
Member

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

т.к. это динамический sql в итоге и мы передаем строки в запрос, то имее лишние заморочки с sql injections.
поэтому думаю что оптимальный вариант это дополниетльный парметр где указывается режим как именно нужно делать поиск. как выше в примере это можно и без динамического sql сделать с минимальным ущербом для производительности (option recompile).
13 сен 18, 15:44    [21673803]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null  [new]
iap
Member

Откуда: Москва
Сообщений: 46983
Владимир Затуливетер
iap,

т.к. это динамический sql в итоге и мы передаем строки в запрос, то имее лишние заморочки с sql injections.
поэтому думаю что оптимальный вариант это дополниетльный парметр где указывается режим как именно нужно делать поиск. как выше в примере это можно и без динамического sql сделать с минимальным ущербом для производительности (option recompile).
Где динамический SQL??
DECLARE @Param VARCHAR(100)='38,-56,0,15';

SELECT Value FROM ...
WHERE @Param='ANY'
   OR @Param='NULL' AND Value IS NULL
   OR @Param='NOT NULL' AND Value IS NOT NULL
   OR ','+@Param+',' LIKE '%,'+CAST(Value AS VARCHAR)+',%';
Где тут динамика?
13 сен 18, 16:14    [21673842]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20538
iap, а что будет, если в @Param передать честный NULL? юзер - он ведь существо творческое...
13 сен 18, 16:30    [21673869]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null  [new]
Владимир Затуливетер
Member

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

а ну если так, то динамики нет.

но есть другие пробелемы/вопросы:
1. что если необходимо искать по совпадению начала строки, like 'asdf%'?
2. как быть с другими типами? конвертить дату и числа в строку?
3. как поддержать другие операции > < != ?

ну и напоследок, а чем собственно плох вариант с доп параметром?
вы ведь тоже самое делаете, только пытаетесь в одном параметре держать и поведение и значение, в чем выигрыш вашего подхода?
13 сен 18, 16:38    [21673884]     Ответить | Цитировать Сообщить модератору
 Re: Как параметризовать запрос по условию is null / is not null ?  [new]
WaspNewCore
Member

Откуда:
Сообщений: 182
Решение Владимир Затуливетера мне кажется чище, читабельней. И стандартно проще работать с int параметром, чем проверять строковые параметры, где нужно будет учитывать регистр.
13 сен 18, 17:03    [21673933]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить