sql - 参数化 WHERE 子句？

Question

我需要为 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 的结构?

Answer 1

尝试这个

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