关于where语句

[riconyi]

select * from job where id=@id and jobid=@jobid and comid=@comid
现在的问题是
如果@jobid为空的话，我能否就用一个判断语句使and jobid=@jobid 不出现从而变成
select * from job where id=@id and comid=@comid
同样也有可能@comid也为空，或者@jobid和@comid都为空，那就变成
select * from job where id=@id
请问这种功能能利用innull函数实现吗？如果不能，还有别的办法没？
因为这里只举出了几种变量，另外还有好多，我不想在select语句前面判断再来确定where语句，那样子的话，如果我有6个变量，岂不是要判断6*6=36次？

[nononono]

没有6*6那么多吧。这样行不？

select * from job where id=@id
and (@jobid is null or jobid=@jobid)
and (@comid is null or comid=@comid)

[冷箫轻笛]

这样行不行

select * from job where id=@id
and isnull(jobid,@jobid)=@jobid and isnull(comid,@jobid)= @jobid

[冷箫轻笛]

这样行不行

select * from job where id=@id
and isnull(jobid,@jobid)=@jobid and isnull(comid,@jobid)= @jobid

[yesyesyes]

不行

[churchatp1]

select * from job where id=@id and jobid=(case when @jobid is null then jobid else @jobid end) and comid=(case when @comid is null then comid else @comid end )
试试看这样，这样应该可以吧

[churchatp1]

试试看这样，肯定可以了:
select * from job where id=isnull(@id,id) and jobid=isnull(@jobid,jobid) and comid=(@comid,comid)

[vivianfdlpw]

select * from job
where id=isnull(@id,id)
and
jobid=isnull(@jobid,jobid)
and
comid=isnull(@comid,comid)

[riconyi]

看上去楼上的这种方法应该可以，但是为什么会没有任何数据呢？

[riconyi]

up