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

Откуда: Санкт-Петербург
Сообщений: 348
Добрый день, коллеги.

Есть необходимость написания процедуры с несколькими вариативными параметрами. Т.е. если передают пусто, то получаем полный набор, если опеределенное значение параметра, то фильтруем по нему. и таких несколько. Я решил эту задачу так... подскажите может быть есть другие идеи (наверняка есть)?



IF @Param2014=''
BEGIN
INSERT INTO #Temp1
SELECT * FROM #Temp
END
ELSE
BEGIN
INSERT INTO #Temp1
SELECT * FROM #Temp WHERE [Товар2014]=@Param2014
END;

IF @INN=''
BEGIN
INSERT INTO #Temp2
SELECT * FROM #Temp1
END
ELSE
BEGIN
INSERT INTO #Temp2
SELECT * FROM #Temp1 WHERE ИНН=@INN
END;

IF @KBK=''
BEGIN
INSERT INTO #Temp3
SELECT * FROM #Temp2
END
ELSE
BEGIN
INSERT INTO #Temp3
SELECT * FROM #Temp2 WHERE [КБК]=@KBK
END;
SELECT * FROM #Temp3

DROP TABLE #Temp1,#Temp2,#Temp3,#Temp
6 май 14, 18:02    [15981917]     Ответить | Цитировать Сообщить модератору
 Re: Несколько параметров фильтра  [new]
Кавказ-сила
Member

Откуда: Москва
Сообщений: 261
MedBrat
может быть есть другие идеи
https://www.sql.ru/faq/faq_topic.aspx?fid=114
6 май 14, 18:44    [15982151]     Ответить | Цитировать Сообщить модератору
 Re: Несколько параметров фильтра  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8807
where isnull(param1,field1) = field1
7 май 14, 11:46    [15984509]     Ответить | Цитировать Сообщить модератору
 Re: Несколько параметров фильтра  [new]
MedBrat
Member

Откуда: Санкт-Петербург
Сообщений: 348
Владислав Колосов
where isnull(param1,field1) = field1

не совсем понимаю конструкцию. Если переменная параметр 1 = null, то подменяем ее на поле1, а концовка = поле 1... это как?
7 май 14, 14:43    [15985780]     Ответить | Цитировать Сообщить модератору
 Re: Несколько параметров фильтра  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
MedBrat
Владислав Колосов
where isnull(param1,field1) = field1

не совсем понимаю конструкцию. Если переменная параметр 1 = null, то подменяем ее на поле1, а концовка = поле 1... это как?
Прикалываетесь?
Однако, такая конструкция логически неверна: ни при каких условиях не удастся получить field1 IS NULL.
Лучше, конечно,
WHERE @param1 IS NULL OR field1=@param1
А вот такую конструкцию понимаете:
WHERE field1 = ALL(SELECT @param1 WHERE @param1 IS NOT NULL)
?
7 май 14, 14:51    [15985838]     Ответить | Цитировать Сообщить модератору
 Re: Несколько параметров фильтра  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
iap
Лучше, конечно,
WHERE @param1 IS NULL OR field1=@param1
более читаемо - возможно
но и то и другое будет сканить индекс
7 май 14, 14:53    [15985853]     Ответить | Цитировать Сообщить модератору
 Re: Несколько параметров фильтра  [new]
Sergey Sizov
Member

Откуда:
Сообщений: 1578
Гадя Петрович
iap
Лучше, конечно,
WHERE @param1 IS NULL OR field1=@param1
более читаемо - возможно
но и то и другое будет сканить индекс
Далеко не факт. Последние версии сервера эту функцию "знают" и не сканят.
7 май 14, 14:56    [15985879]     Ответить | Цитировать Сообщить модератору
 Re: Несколько параметров фильтра  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Гадя Петрович
iap
Лучше, конечно,
WHERE @param1 IS NULL OR field1=@param1

более читаемо - возможно
но и то и другое будет сканить индекс
Тогда - много-много UNION ALL
7 май 14, 14:58    [15985902]     Ответить | Цитировать Сообщить модератору
 Re: Несколько параметров фильтра  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
Sergey Sizov
Гадя Петрович
пропущено...
более читаемо - возможно
но и то и другое будет сканить индекс
Далеко не факт. Последние версии сервера эту функцию "знают" и не сканят.
последние - это 2012? Ничего себе, до чего техника дошла!
7 май 14, 14:59    [15985915]     Ответить | Цитировать Сообщить модератору
 Re: Несколько параметров фильтра  [new]
Sergey Sizov
Member

Откуда:
Сообщений: 1578
Гадя Петрович
Sergey Sizov
пропущено...
Далеко не факт. Последние версии сервера эту функцию "знают" и не сканят.
последние - это 2012? Ничего себе, до чего техника дошла!
Не-а, раньше. Я уже не помню на какой именно версии тестировал, то ли 2005, то ли 2008.
7 май 14, 15:03    [15985953]     Ответить | Цитировать Сообщить модератору
 Re: Несколько параметров фильтра  [new]
хмхмхм
Guest
Гадя Петрович
Sergey Sizov
пропущено...
Далеко не факт. Последние версии сервера эту функцию "знают" и не сканят.
последние - это 2012? Ничего себе, до чего техника дошла!


На Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Скан есть.

На Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Скан есть.
7 май 14, 15:04    [15985963]     Ответить | Цитировать Сообщить модератору
 Re: Несколько параметров фильтра  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
Sergey Sizov
Гадя Петрович
пропущено...
последние - это 2012? Ничего себе, до чего техника дошла!
Не-а, раньше. Я уже не помню на какой именно версии тестировал, то ли 2005, то ли 2008.
эээ
тогда очень мне сомнительны эти заявления
я на 2008 никакими силами не смог заставить оптимизатор отказаться от сканов на подобных запросах
7 май 14, 15:04    [15985964]     Ответить | Цитировать Сообщить модератору
 Re: Несколько параметров фильтра  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
Гадя Петрович,

дык, разгадка проста, вроде. option (recompile) в запрос добавить. последствия понимаете сами.
7 май 14, 15:09    [15986009]     Ответить | Цитировать Сообщить модератору
 Re: Несколько параметров фильтра  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
daw
Гадя Петрович,

дык, разгадка проста, вроде. option (recompile) в запрос добавить. последствия понимаете сами.
хинтами любой дурак сможет, можно и forceseek сказать
вопрос то не в этом
7 май 14, 15:11    [15986028]     Ответить | Цитировать Сообщить модератору
 Re: Несколько параметров фильтра  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
daw,

а так, чудес-то не бывает: как бы это поиск для случая @param is null использовать? но можно, строить разные планы в зависимости от. только план тогда при каждом выполнении строить придется.
7 май 14, 15:13    [15986039]     Ответить | Цитировать Сообщить модератору
 Re: Несколько параметров фильтра  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8807
автор
не совсем понимаю конструкцию. Если переменная параметр 1 = null, то подменяем ее на поле1, а концовка = поле 1... это как?


1. @param1 = null
where filed1 = fileld1 -- все

2. @param1 not null
where @param1 = field1 -- точное значение

все field1 is null будут удалены из результата в любом случае.
7 май 14, 16:42    [15986603]     Ответить | Цитировать Сообщить модератору
 Re: Несколько параметров фильтра  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2793
daw
Гадя Петрович,

дык, разгадка проста, вроде. option (recompile) в запрос добавить. последствия понимаете сами.

Только что проверил: у меня на 2012 и с option (recompile) скан индекса при @param1 IS NULL OR field1=@param1
10 май 14, 09:51    [15996776]     Ответить | Цитировать Сообщить модератору
 Re: Несколько параметров фильтра  [new]
aleks2
Guest
set showplan_all on
go
declare @t table (n int primary key clustered);

insert @t values(1), (2), (3), (4), (5), (6), (7), (8)

declare @n int = 5;


select * from @t where n = @n 
union all
select * from @t where @n is null

go
set showplan_all off
11 май 14, 09:59    [15999399]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить