Create table #table1(Parameter1 varchar(20),Parameter2 varchar(20),Parameter3 int)
insert into #table1
select '年资' Parameter1,'>=' Parameter2, 6 Parameter3
union
select '学历' Parameter1,'>=' Parameter2, 8 Parameter3
Create table #table2(Empname varchar(20),年资 int)
Create table #table3(Empname varchar(20),学历 int)insert into #table2
select 'A' Empname,8 年资
union
select 'B' Empname,7 年资 insert into #table3
select 'A' Empname,9 学历
union
select 'B' Empname,14 学历 #table1中存 条件,可增加
#table2,#table3存具体每个人的信息
结果 要查出所有符合#table1中的条件的人员EmpName
declare @sql varchar(max)
declare @para1 varchar(max)
declare @para2 varchar(max)
declare @para3 varchar(max)
set @sql='select table2.empname from table2 inner join table3 on table2.empname=table3.empname where 1=1 '
declare cur Cursor Fast_Forward For
Select parameter1,parameter2,parameter3 from table1
Open cur
Fetch Next from cur into @para1, @para2, @para3
While @@FETCH_STATUS = 0 Begin
set @sql = @sql +' and '+@para1+@para2+@para3
Fetch Next from cur into @para1, @para2, @para3
End
Close cur
Deallocate cur
exec (@sql)
declare @sql varchar(max)
set @sql='select table2.empname from table2 inner join table3 on table2.empname=table3.empname where 1=1 '
select @sql= @sql+ ' and '+parameter1+parameter2+cast(parameter3 as varchar) from table1
exec (@sql)
set @sql='select table2.empname from table2 a inner join table3 b on a.empname=b.empname where 1=1 '
select @sql= @sql+ ' and '+parameter1+parameter2+cast(parameter3 as varchar(max)) from table1
exec (@sql)
set @sql='select a.empname
from #table2 a
inner join #table3 b
on a.empname=b.empname
where 1=1 '
select @sql= @sql+ ' and ('+parameter1+parameter2+cast(parameter3 as varchar(max))+')' from #table1
--PRINT @sql
exec (@sql)
/*
A
B
*/
--#1.如果#table1表为空,where 1=1 可使SQL语句不会报错.
--#2.最好用nvarchar.
之后加的条件要判断是不是第一个条件是则不要加and 不是则要加and
用了1=1
后面的条件都加and不用再去判断