SQL动态条件查询例子

set nocount on
--数据--------------------------------------------------------------------------

create table [A] ([id] int,[name] varchar(4),[sex] varchar(2))
insert into [A] select 1,'jack','男'
insert into [A] select 2,'Tom','女'
insert into [A] select 3,'jons','女'
insert into [A] select 10,'jfns','女'
go
--条件有:包含,不包含,等于,大于等于,小于等于,不等于,小于,大于等
--代码--------------------------------------------------------------------------
declare @col varchar(10),@t varchar(10),@d varchar(10)
declare @sql varchar(1000)
select @col = 'name',@t = '包含',@d = 'o'
set @sql = 'select * from a where '+rtrim(@col)
+case @t when '包含'    then ' like [email=]''%'+@d+'%'''[/email]
         when '不包含'  then ' not like [email=]''%'+@d+'%'''[/email]
         when '等于'    then [email=]'='''+@d+''''[/email]
         when '不等于'  then '<>'''+@d+''''
         when '大于等于'then '>='''+@d+''''
         when '小于等于'then '<[email==]='''+@d+''''[/email]
         when '小于'    then '<[email==]='''+@d+''''[/email]
         when '大于'    then '<[email==]='''+@d+''''[/email] end
select @col = 'id',@t = '大于等于',@d = '3'
print @sql
exec(@sql)
go
/*结果--------------------------------------------------------------------------
select * from a where name like '%o%'
id          name sex  
----------- ---- ----
2           Tom  女
3           jons 女
select * from a where id>='3'
id          name sex  
----------- ---- ----
3           jons 女
10          jfns 女
--清除------------------------------------------------------------------------*/
drop table a