如何在where子句中获取空值

问题描述

你好朋友，

我有两个桌子

Hello friends,

I have two table

1. FWB_SF_Opportunity (Does not contain VCID)<br />
2. FWB_VWSFData (Contains column VCID)

我用以下查询制作了SP:

I had made SP with following query:

SELECT * FROM (
SELECT * FROM FWB_SF_Opportunity B
left join FWB_VWSFData A ON A.opportunityid=B.id and ReviewMonth=@ReviewMonth
) T
where T.VCID = isnull(@vcid,T.vcid)

参数@VCID可以采用值5(vcid = 5的记录)或null(获取所有记录，包括VCID值为null的值)

问题是当@VCID = NULL时，结果应包含所有记录以及VCID，其值为空.但是在这种情况下，where条件失败，它仅给出VCID = 5且不为null的记录.

有什么解决办法吗?

在此先感谢

The parameter @VCID can take values 5(records whose vcid = 5) or null(gets all records, including VCID whose value is null)

Problem is when @VCID = NULL, the result should contain all records also VCID whose value is null. But the where condition fails in this case, it only gives record whose VCID = 5 and not null.

any solution?

thanks in advance

推荐答案

SELECT * FROM (
SELECT * FROM FWB_SF_Opportunity B
left join FWB_VWSFData A ON A.opportunityid=B.id and ReviewMonth=@ReviewMonth
) T
where
((isnull(@VCID,'') = '' AND 1 = 1)
 OR
 (isnull(@VCID,'') <> '' AND T.VCID = @VCID))

而且效果很好
希望遇到同样情况的人们能从中得到帮助

快乐的编码:)

And it works fine
Hope guys who face same situation, get help out of it

Happy coding :)

这篇关于如何在where子句中获取空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！