Parametrize the WHERE clause?

Posted by ControlFlow on Stack Overflow See other posts from Stack Overflow or by ControlFlow
Published on 2010-06-10T13:28:16Z Indexed on 2010/06/10 13:32 UTC
Read the original article Hit count: 343

Hi, stackoverflow!

I'm need to write an stored procedure for SQL Server 2008 for performing some huge select query and I need filter it results with specifying filtering type via procedure's parameters (parameterize where clause). 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';

But it doesn't work when the columns has different data types... It's possible to cast all the columns to nvarchar(max) and compare they as strings, but I think it will cause a performance degradation...

Is it possible to parameterize where clause in stored procedure without using things like EXEC sp_executesql (dynamic SQL and etc.)?

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server-2008