Параметризовать оператор Where?
Я - потребность записать хранимую процедуру для SQL Server 2008 для выполнения большого select для запроса и меня нужен он для фильтрации результатов с определением типа фильтрации через параметры процедуры. Я нашел некоторые решения как это:
create table Foo(
id bigint, code char, name nvarchar(max))
go
insert into Foo values
(1,'a','aaa'),
(2,'b','bbb'),
(3,'c','ccc')
go
create procedure Bar
@FilterType nvarchar(max),
@FilterValue nvarchar(max) as
begin
select * from Foo as f
where case @FilterType
when 'by_id' then f.id
when 'by_code' then f.code
when 'by_name' then f.name end
=
case @FilterType
when 'by_id' then cast(@FilterValue as bigint)
when 'by_code' then cast(@FilterValue as char)
when 'by_name' then @FilterValue end
end
go
exec Bar 'by_id', '1';
exec Bar 'by_code', 'b';
exec Bar 'by_name', 'ccc';
Я нахожу, что этот подход не работает. Возможно бросить все столбцы к nvarchar(max) и сравните их как строки, но я думаю, что это вызовет снижение производительности.
Действительно ли возможно параметризовать where пункт в хранимой процедуре, не используя конструкции как EXEC sp_executesql?
4 ответа
Это может стать немного более длинным из-за больших требований к фильтрам, но я думаю, что это, вероятно, более производительно / легче читать / поддерживать:
create procedure Bar
@Id int,
@Code nvarchar,
@name nvarchar
begin
select * from Foo as f
where (@id = -1 or f.ID = @id)
and (@Code = '' or f.Code = @Code)
and (@Name = '' or f.Name = @Name)
end
go
exec Bar 1, '', ''
exec Bar -1, 'code', ''
exec Bar -1, '', 'name'
Это также позволяет вам фильтровать более чем один элемент одновременно.
declare @field varchar(20)
declare @value varchar(100)
set @field = 'customerid'
set @value = 'ALFKI'
set @field = 'Country'
set @value = 'Germany'
set @field = 'ContactTitle'
set @value = 'Owner'
select * from customers
where (customerid = (case when @field = 'customerid' then @value else customerid end)
and ContactTitle = (case when @field = 'ContactTitle' then @value else ContactTitle end)
and country = (case when @field = 'Country' then @value else country end))
Пример адаптирован для базы данных Northwind.
Надеюсь, это поможет.
EDIT: Закомментируйте любые 2 из 3 значений для @field и @value.
Попробуйте это
create procedure Bar
@FilterType nvarchar(max),
@FilterValue nvarchar(max) as
begin
select * from Foo as f
where
(@FilterType ='by_id' and f.id =cast(@FilterValue as bigint) )
OR
(@FilterType ='by_code' and f.code =cast(@FilterValue as char)
OR
(@FilterType ='by_name' and f.name =@FilterValue
end
go
Если у вас не так много критериев фильтрации, вы можете подумать о создании делегированных функций и отправке запроса соответствующему. Например,
create procedure Bar
@FilterType nvarchar(max),
@FilterValue nvarchar(max) as
begin
case @FilterType
when 'by_id' then FilterById(@FilterValue)
when 'by_code' then FilterByCode(@FilterValue)
when 'by_name' then FilterByName(@FilterValue)
end
end
go