Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 запрос с переменным числом параметров  [new]
hey
Guest
требуется выполнять поиск по базе пользователей, причем с переменным числом параметров. Т.е. оператор может ввести только фамилию, или фамилию + имя + номер квартиры и т.д.

Просто динамически сгенерировать запрос сложив все заполненные поля формы нельзя, т.к. будет вероятность sql-ejection. Т.е. нужна ХП с параметрами, как это реализовывается ? наверняка распостраненная проблема ...
15 июл 05, 15:34    [1708932]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
select <..>
from <..>
where
(@param1 is null or <..> = @param1) and
(@param2 is null or <..> = @param2)
...
15 июл 05, 15:36    [1708945]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
chenosov
Member

Откуда: С.Петербург
Сообщений: 272
Пример из жизни
ALTER                     Proc p_fmTermCntrsNew_Read 
	@Type int,
	@IsOnGround bit=0, 
	@IsDate int=0,
	@Date smalldatetime=null, 
	@Date1 smalldatetime=null, 
	@id_Line int=null,
	@id_Node int=null,
	@Id_cntr int=null,
	@str varchar(50)=null,
	@id_Node1 int=204,
	@id_Stat int=null
AS
set nocount on
select * from @w w
 from tbl_CntrsInput ci 
left join tbl_CntrsOutput co on ci.id_CntrsInput=co.id_CntrsInputChild
inner join tbl_Cntrs c on c.id_Cntr=ci.id_Cntr
where ci.id_Node=@id_Node1
and ci.Id_cntr=case when @Id_cntr is null then ci.Id_cntr else @Id_cntr end
and isnull(c.id_Line,0)=case when @id_Line is null then isnull(c.id_Line,0) else @id_Line end
and isnull(co.id_NodeHandling,0)=case when @id_Node is null then isnull(co.id_NodeHandling,0) else @id_Node end
and isnull(ci.id_TypeCntrState,0)=case when @id_Stat is null then isnull(ci.id_TypeCntrState,0) else @id_Stat end
Причем, если поле может иметь значение NULL, то не забывайте и для него писать Isnull, иначе сравнение не сработает
15 июл 05, 15:45    [1709023]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
https://www.sql.ru/faq/faq_topic.aspx?fid=114
15 июл 05, 15:47    [1709041]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541

А я бы все
case when @Id_cntr is null then ci.Id_cntr else @Id_cntr end 
заменил на
coalesce(@Id_cntr, 
ci.Id_cntr)


Posted via ActualForum NNTP Server 1.2

15 июл 05, 15:56    [1709109]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
chenosov
Пример из жизни

А вот столбцы по-возможности не стоит в функции заключать
15 июл 05, 15:56    [1709111]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541

Да и
and isnull(c.id_Line,0)=case when @id_Line is null then isnull(c.id_Line,0) else @id_Line end
заменил бы тоже на
and coalesce(c.id_Line,0) = coalesce(@id_Line, c.id_Line, 0)


Posted via ActualForum NNTP Server 1.2

15 июл 05, 15:59    [1709128]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
chenosov
Member

Откуда: С.Петербург
Сообщений: 272
к стыду своему только сейчас узнал про COALESCE. А в 7 версии она была?
Ray D
А вот столбцы по-возможности не стоит в функции заключать

Поясните, что-то я не понял.
15 июл 05, 16:11    [1709210]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541

chenosov
только сейчас узнал про COALESCE. А в 7 версии она была?

Да, была.

Posted via ActualForum NNTP Server 1.2

15 июл 05, 16:15    [1709243]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Ray D
Member

Откуда: from the middle of nowhere
Сообщений: 3598
Блог
Если столбец завернут в функцию, то индекс по нему использоваться не будет.
Так что конструкцию:
isnull(c.id_Line,0)=case when @id_Line is null then isnull(c.id_Line,0) else @id_Line end
лучше заменить на что-то типа:
(@id_Line is null or c.id_line = @id_line)
15 июл 05, 16:16    [1709250]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541

Ray D
Если столбец завернут в функцию, то индекс по нему использоваться не будет.

С чего ты это взял?

Posted via ActualForum NNTP Server 1.2

15 июл 05, 16:24    [1709301]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541

2 RayD: Для примера можешь сравнить планы двух запросов:

create table #t (id int primary key identity, f int, val int)

create index t_ix on #t(val, f)

insert #t (f, val)
select 1, null union all
select 2, 1 union all
select 3, 2 union all
select 4, null union all
select 5, 3

declare @val int
set @val = 1

select val, f
from #t
where coalesce(val,0) = coalesce(@val, val, 0)

select val, f
from #t
where @val is null or @val = val

drop table #t


Posted via ActualForum NNTP Server 1.2

15 июл 05, 16:28    [1709328]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Glory
Member

Откуда:
Сообщений: 104760
Roman S. Golubin

Ray D
Если столбец завернут в функцию, то индекс по нему использоваться не будет.

С чего ты это взял?

Posted via ActualForum NNTP Server 1.2

С того, что индексный ключ построен бех использования функции
И оптимизатор не будет разбирать, насколько результат функции подходит под индексный ключ. Т.к. при построении плана никакие функуии/выражения запроса не вычисляются
15 июл 05, 16:34    [1709378]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541

То есть ты хочешь сказать что при val is null никаких расчетов не производится, а при coalesce(val,0) = 0 производятся расчеты?
:-)))

Posted via ActualForum NNTP Server 1.2

15 июл 05, 16:39    [1709406]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Glory
Member

Откуда:
Сообщений: 104760
2 RayD: Для примера можешь сравнить планы двух запросов:
Ваш индекс t_ix ничем не отличается от самой таблицы, т.к. содержит все ее столбцы
15 июл 05, 16:40    [1709414]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Glory
Member

Откуда:
Сообщений: 104760
Roman S. Golubin

То есть ты хочешь сказать что при val is null никаких расчетов не производится, а при coalesce(val,0) = 0 производятся расчеты?
:-)))

Posted via ActualForum NNTP Server 1.2

Я хочу сказать что при _создании плана_ никакие функции не вычисляеются.
15 июл 05, 16:42    [1709431]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541

Glory
Я хочу сказать что при _создании плана_ никакие функции не вычисляются.

И при чем тут создание плана?
Я говорю что в обоих случаях (смотри пример выше) индекс _БУДЕТ_ использоваться.

Posted via ActualForum NNTP Server 1.2

15 июл 05, 16:50    [1709491]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
злой шаман
Member

Откуда: Питер
Сообщений: 1253
Glory
Я хочу сказать что при _создании плана_ никакие функции не вычисляеются.

Coalesce не функция, а макроопределение. По идее, оно раскрывается оптимизатором до построения плана.
15 июл 05, 16:55    [1709539]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Glory
Member

Откуда:
Сообщений: 104760
злой шаман
Glory
Я хочу сказать что при _создании плана_ никакие функции не вычисляеются.

Coalesce не функция, а макроопределение. По идее, оно раскрывается оптимизатором до построения плана.

макроопределение ? Ссылку какую нибудь можно ?

Roman S. Golubin

Glory
Я хочу сказать что при _создании плана_ никакие функции не вычисляются.

И при чем тут создание плана?
Я говорю что в обоих случаях (смотри пример выше) индекс _БУДЕТ_ использоваться.

ваш индекс включает ВСЕ столбцы таблицы. Поэтому ваш Index Scan = TableScan
15 июл 05, 17:03    [1709599]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
Roman S. Golubin

Glory
Я хочу сказать что при _создании плана_ никакие функции не вычисляются.

И при чем тут создание плана?
Я говорю что в обоих случаях (смотри пример выше) индекс _БУДЕТ_ использоваться.

Posted via ActualForum NNTP Server 1.2

Использовать индекс можно по разному.
В одном случае есть у оптимизатора будет вероятность использовать индекс seek, а во втором индекс юзается, видимо, потому что https://www.sql.ru/forum/actualpost.aspx?bid=1&tid=200622&mid=1709491&p=1&act=quot#1709414

Сравните:
declare @val varchar(20)
set @val = 'sysobjects'
select *
from sysobjects
where name = @val

select *
from sysobjects
where coalesce(name, '') = coalesce(@val, name, '')
15 июл 05, 17:08    [1709641]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
WiRuc
Member

Откуда: Воронеж
Сообщений: 1280
Да какая разница, все равно при таких запросах вы не получите IndexSeek, в лучшем случае IndexScan.
15 июл 05, 17:09    [1709649]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
WiRuc
Member

Откуда: Воронеж
Сообщений: 1280
Гавриленко Сергей Алексеевич
Roman S. Golubin

Glory
Я хочу сказать что при _создании плана_ никакие функции не вычисляются.

И при чем тут создание плана?
Я говорю что в обоих случаях (смотри пример выше) индекс _БУДЕТ_ использоваться.

Posted via ActualForum NNTP Server 1.2

Использовать индекс можно по разному.
В одном случае есть у оптимизатора будет вероятность использовать индекс seek, а во втором индекс юзается, видимо, потому что https://www.sql.ru/forum/actualpost.aspx?bid=1&tid=200622&mid=1709491&p=1&act=quot#1709414

Сравните:
declare @val varchar(20)
set @val = 'sysobjects'
select *
from sysobjects
where name = @val

select *
from sysobjects
where coalesce(name, '') = coalesce(@val, name, '')


Вы привели в качестве примера два РАЗНЫХ по смыслу запроса. В качестве первого запроса надо использовать
select *
from sysobjects
where @val is null OR name = @val
и в обоих случаях вы получите IndexScan.
15 июл 05, 17:12    [1709673]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
2WiRuc: верно
15 июл 05, 17:15    [1709698]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541

WiRuc
Вы привели в качестве примера два РАЗНЫХ по смыслу запроса. В качестве первого запроса надо использовать
select *
from sysobjects
where @val is null OR name = @val

Я бы даже сказал что:
select *
from sysobjects
where @val is null OR (name is not null and name = @val)


Posted via ActualForum NNTP Server 1.2

15 июл 05, 17:17    [1709706]     Ответить | Цитировать Сообщить модератору
 Re: запрос с переменным числом параметров  [new]
Glory
Member

Откуда:
Сообщений: 104760
declare @val sysname
set @val = 'sysobjects'
select id, xtype
from sysobjects
where name = @val or @val is null

select id, xtype
from sysobjects
where coalesce(name, '') = coalesce(@val, name, '')
15 июл 05, 17:20    [1709731]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить