职工表: 姓名(xm) 部门编号(bm) 职称编号(zc)设计存储过程查询职工:QueryEmployee @xm,@bm,@zc如果@bm=-1,就代表所有部门
如果@zc=-1,就代表所有职称我能想到的就是使用if来判断,if @bm=-1 and @zc=-1
select xm,bm,zc,rzsj
from employee
else if @bm=-1
select xm,bm,zc,rzsj
from employee
where zc=@zc
else if @zc=-1
select xm,bm,zc,rzsj
from employee
where bm=@bm
else
select xm,bm,zc,rzsj
from employee
where bm=@bm and zc=@zc
可是如果再增加一列,又会增加很多判断分支,请问有没有简单的方法,来处理@bm=-1,@zc=-1的情况。谢谢
如果@zc=-1,就代表所有职称我能想到的就是使用if来判断,if @bm=-1 and @zc=-1
select xm,bm,zc,rzsj
from employee
else if @bm=-1
select xm,bm,zc,rzsj
from employee
where zc=@zc
else if @zc=-1
select xm,bm,zc,rzsj
from employee
where bm=@bm
else
select xm,bm,zc,rzsj
from employee
where bm=@bm and zc=@zc
可是如果再增加一列,又会增加很多判断分支,请问有没有简单的方法,来处理@bm=-1,@zc=-1的情况。谢谢
select xm,bm,zc,rzsj
from employee
where (bm=@bm or @bm=-1)
and (zc=@zc or @zc=-1)
from employee
where
case when @bm = -1 and @zc = -1 then zc=zc and bm=bm end
and case when @zc = -1 and @bm <> -1 then bm=@bm
and case when @bm = -1 and @zc <> -1 then zc=@zc
else then bm=@bm and zc=@zc end
from employee
where (@bm=-1 and @zc=-1) or
(@bm=-1 and @zc<>-1 and zc=@zc) or
(@bm<>-1 and @zc=-1 and bm=@bm) or
( @bm<>-1 and @zc<>-1 and bm=@bm and zc=@zc)
declare @sql varchar(1000)
set @sql='select xm,bm,zc,rzsj from employee where 1=1'
if(@zc!=-1)
set @sql=@sql+' and zc='''+@zc+''''
if(@bm!=-1)
set @sql=@sql+' and bm='''+@bm+''''
exec(@sql)
--或者最简单的方式select xm,bm,zc,rzsj from employee where zc=isnull(nullif(@zc,-1),zc) and bm=isnull(nullif(@bm,-1),bm)