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