Parametrize the WHERE clause?

Question

I'm need to write a stored procedure for SQL Server 2008 for performing a large select query and I need it to filter results with specifying filtering type via procedure's parameters. I found some solutions like this:

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';

I'm finding that this approach doesn't work. It's possible to cast all the columns to nvarchar(max) and compare them as strings, but I think it will cause performance degradation.

Is it possible to parametrize the where clause in stored procedure without using constructs like EXEC sp_executesql?

score 2 · Answer 1 · answered Jun 10 '10 at 13:39

2

Try this

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

answered Jun 10 '10 at 13:39

Madhivanan

12,915
1
21
26

thank you, but it doesn't works... SQL Server tries to execute all the `and` branches and fails to convert data on the right sides... – controlflow Jun 10 '10 at 14:37
Are you sure? The OR part will take care of it – Madhivanan Jun 10 '10 at 15:01

score 2 · Accepted Answer · answered Jun 10 '10 at 13:53

2

This may become a little more long winded, for large filter requirements, but I think it probably more performant/easier to read/maintain:

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'

This also allows you to filter by more than one item at the same time.

answered Jun 10 '10 at 13:53

Paddy

31,468
14
75
108

I think you could replace the outer "and"s with ors, and drop the first part of the or clause, like so: ...where f.ID = @id or f.Code = @Code or f.Name = @Name. (This would work with nulls, unless you were searching for columns that were set to null.) – Philip Kelley Jun 10 '10 at 14:07
@Philip Kelley - Depends on what you want the filter to do - if you want to return where you match on any of the fields passed, then that'd work. – Paddy Jun 10 '10 at 14:39

score 0 · Answer 3 · answered Jun 10 '10 at 13:51

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))

Example is adapted for Northwind database.
Hope this helps.

EDIT: Comment any 2 out of the 3 values for @field and @value for above.

score 0 · Answer 4 · answered Jun 10 '10 at 13:55

If you don't have many filtering criteria, you could consider creating delegate functions and dispatching the request to the appropriate one. E.g.,

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

Parametrize the WHERE clause?

4 Answers4