求一种方法来简化SQL查询

[jilm168]

职工表: 姓名(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的情况。谢谢

[feilniu]

select xm,bm,zc,rzsj
from employee
where (bm=@bm or @bm=-1)
  and (zc=@zc or @zc=-1)

[王向飞]

select xm,bm,zc,rzsj
    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
 

[情殇无限]

select xm,bm,zc,rzsj 
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)

[pt1314917]

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)

[pt1314917]

--或者最简单的方式

select xm,bm,zc,rzsj from employee where zc=isnull(nullif(@zc,-1),zc) and bm=isnull(nullif(@bm,-1),bm)

[SQLCenter]

1楼最简单，逻辑清晰。

[jilm168]

没想到会有这么多方法,谢谢大家. 1楼和5楼,方法都不错.

[GUOCHENGJUN]

[Quote=引用 2 楼 wxf163 的回复:]
SQL code
select xm,bm,zc,rzsj
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 an……
[/Quote]
UP------------------