SQLSERVER更优化的判断条件查询方法测试

[x1234521]

--更优化的判断条件查询方法测试

--==========create test ===============
create table xjj (col varchar(50))

insert into xjj select 'a'
union all select 'b'
union all select 'c'

declare @p varchar(50)
set @p = ''--change this value (a or b or c or '') to test.
---------------------------------

--==========method 1==========
if @p=''
select * from xjj
else
select * from xjj where col=@p

--==========method 2==========
declare @sql varchar(500)
set @sql='select * from xjj'
if @p<>''
set @sql = @sql+' where col='''+@p+''''
execute(@sql)

--==========method 3==good!!========
select * from xjj where (case when @p<>'' then col else @p end)=@p

--drop table
drop table xjj

留印备忘，顺便散点分！

[AcHerat]

接分，楼主，沙发100！

[中国风]

--method 4 very good--
select * from xjj 
where  @p=''  OR col=@p

[x1234521]

这个也不错~~

[叶子]

[Quote=引用 2 楼 roy_88 的回复:]

SQL code
--method 4 very good--
select * from xjj
where @p='' OR col=@p
[/Quote]
支持这个...

[好汉坡]

用sp_executesql