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

Откуда:
Сообщений: 66
Здравствуйте!

Хочу создать хранимую процедуру. В нее будут передаваться параметры, например так:
CREATE PROCEDURE [dbo].[comments_select]
	@article int,
	@user_name nvarchar(50),
	@date_time datetime,
	@comment nvarchar(1000),
	@answer_id int,
	@ip nvarchar(50)
AS
SELECT * FROM comments WHERE 
article=@article 
AND user_name=@user_name 
AND date_time=@date_time 
AND comment=@comment 
AND answer_id=@answer_id 
AND ip=@ip 


но иногда часть параметров будет отсутствовать, а иногда даже ни одного параметра не будет и тогда запрос будет выглядеть, например, так:

CREATE PROCEDURE [dbo].[comments_select]
AS
SELECT * FROM comments 


Как правильно построить запрос чтобы он всегда обрабатывался корректно, независимо от того, сколько в него передается параметров?
3 янв 13, 05:31    [13718173]     Ответить | Цитировать Сообщить модератору
 Re: как сделать SELECT с переменным числом парметров  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37228
https://www.sql.ru/faq/faq_topic.aspx?fid=114
3 янв 13, 07:49    [13718207]     Ответить | Цитировать Сообщить модератору
 Re: как сделать SELECT с переменным числом парметров  [new]
kirser
Member

Откуда: Киев
Сообщений: 225
rusik48,
CREATE PROCEDURE [dbo].[comments_select]
	@article int,
	@user_name nvarchar(50),
	@date_time datetime,
	@comment nvarchar(1000),
	@answer_id int,
	@ip nvarchar(50)
AS
SELECT * FROM comments WHERE 
((@article IS NOT NULL AND article=@article) OR (@article IS NULL))
AND ((@user_name IS NOT NULL AND user_name=@user_name) OR (@user_name IS NOT NULL)) 
...
3 янв 13, 12:33    [13718512]     Ответить | Цитировать Сообщить модератору
 Re: как сделать SELECT с переменным числом парметров  [new]
Владимир Затуливетер
Member

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

Достаточно вот так сделать, лишние проверки ни к чему.
Еще надо бы добавить option (recompile) к запросу, иначе план будет один, и он естествено не подходит под все варианты переданных параметров. Если конечно таблица небольшая то и без recompile можно обойтись.

create procedure [dbo].[comments_select]
    @article int
  , @user_name nvarchar(50)
  , @date_time datetime
  , @comment nvarchar(1000)
  , @answer_id int
  , @ip nvarchar(50)
as 
    select  *
    from    dbo.comments
    where   ( article = @article or @article is null )
            and ( user_name = @user_name or  @user_name is null )
            and ...
    option (recompile) ;         
3 янв 13, 13:12    [13718651]     Ответить | Цитировать Сообщить модератору
 Re: как сделать SELECT с переменным числом парметров  [new]
Владимир Затуливетер
Member

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

А вообще в вашем случае лучше динамический sql.

http://aboutsqlserver.com/2010/08/29/sunday-t-sql-tip-how-to-select-data-with-unknown-parameter-set/
3 янв 13, 13:28    [13718711]     Ответить | Цитировать Сообщить модератору
 Re: как сделать SELECT с переменным числом парметров  [new]
Jovanny
Member

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

Надо указывать параметры по умолчанию, а то при вызове процедуры нужно перечислять все параметры и явно присваиваить им NULL, если этот параметр не задаётся.
Кроме того,

(article=@article OR @article IS NULL)
короче, чем
(@article IS NOT NULL AND article=@article) OR (@article IS NULL)
3 янв 13, 13:57    [13718841]     Ответить | Цитировать Сообщить модератору
 Re: как сделать SELECT с переменным числом парметров  [new]
rusik48
Member

Откуда:
Сообщений: 66
Спасибо всем за помощь.
сделал вот такой запрос, правда еще не проверял его в работе.

create procedure [dbo].[comments_select_or]
    @article int
  , @user_name nvarchar(50)
  , @date_time datetime
  , @comment nvarchar(1000)
  , @answer_id int
  , @ip nvarchar(50)
  , @date1 date
as 
    select  *
    from    dbo.comments
    where   ( article = @article or @article is null )
            and ( user_name = @user_name or  @user_name is null )
            and ( date_time = @date_time or  @date_time is null )
            and ( comment = @comment or  @comment is null )
            and ( answer_id = @answer_id or  @answer_id is null )
            and ( ip = @ip or  @ip is null )
            and ( date1 = @date1 or  @date1 is null )
    option (recompile) ;

Но, получается он не будет использовать индексы?

Владимир Затуливетер
rusik48,

А вообще в вашем случае лучше динамический sql.

http://aboutsqlserver.com/2010/08/29/sunday-t-sql-tip-how-to-select-data-with-unknown-parameter-set/


В этой статье по ссылке написано что вот такие запросы не используют индексы. Но это же совсем не вариант.
То есть остается только динамический SQL ?
3 янв 13, 15:54    [13719299]     Ответить | Цитировать Сообщить модератору
 Re: как сделать SELECT с переменным числом парметров  [new]
Владимир Затуливетер
Member

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

Без опции recompile не будет использовать индексы - да, а если выставим recompile то будет. (Проверьте процедуру то вы уже написали, посмотрите на планы выполнения). Но имеем перекомпиляцию каждый раз, дополнительные миллисекунды... Поэтому динамический в большинстве случаев и предпочтительнее.

про recompile тоже можно посмотреть в блоге Дмитрия Короткевича
http://aboutsqlserver.com/2012/05/18/statement-level-recompilation-with-option-recompile/
3 янв 13, 16:14    [13719374]     Ответить | Цитировать Сообщить модератору
 Re: как сделать SELECT с переменным числом парметров  [new]
iap
Member

Откуда: Москва
Сообщений: 47085
rusik48,

кроме того, такой запрос не поможет, если нужны строки, для которых
   article is null
or user_name is null
or date_time is null
or comment is null
or answer_id is null
or ip is null
or date1 is null
3 янв 13, 16:16    [13719388]     Ответить | Цитировать Сообщить модератору
 Re: как сделать SELECT с переменным числом парметров  [new]
rusik48
Member

Откуда:
Сообщений: 66
CREATE procedure [dbo].[comments_select_or]
    @article int = null
  , @user_name nvarchar(50) = null
as 
    select  *
    from    dbo.comments
    where   ( article = @article or @article is null )
            and ( user_name = @user_name or  @user_name is null )
    option (recompile) ;


вот в таком виде работает.
В предыдущем запросе, не было "= null" и он не работал, если, например, user_name отсутствовал.
Также работает и такой запрос:

CREATE procedure [dbo].[comments_select_if]
    @article int = null
  , @user_name nvarchar(50) = null
as 
	if (@article is null AND @user_name is null)
		select * from dbo.comments
	else if (@article is not null AND @user_name is null)
		select * from dbo.comments where article = @article
	else if (@article is null AND @user_name is not null)
		select * from dbo.comments where user_name = @user_name
	else if (@article is not null AND @user_name is not null)
		select * from dbo.comments where article = @article AND user_name = @user_name


Быстрее вероятно последний запрос, но даже для 2х параметров он уже громоздкий, для 2х - 4 селекта, для 3х будет 9 ну итд
для N будет N в квадрате.
Динамический SQL пока не делал. Как сделаю напишу.

Всем огромное спасибо! И с Новым Годом!!!
4 янв 13, 01:54    [13721420]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить