如何在where子句中获取空值 [英] How to get null values in where clause

问题描述

你好朋友，

我有两个桌子

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

推荐答案

AFAIK，或者您必须具有重复的代码才能实现它，或者您可以在代码为NULL的情况下传递其他内容(在查询过程中)参数创建)并处理SP中的已知值，例如在NULL时传递-1.这将确保您的查询参数始终具有有效值，可根据您的需要导致查询结果.

AFAIK, either you have to have a repeating code to achieve it OR you can pass on something else in case of NULL from your code (during query param creation) and handle the known value in SP, like pass -1 when it''s NULL. This will make sure your query param always has a valid value leading to query result as per your need.

我尝试了以下操作

I tried following

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子句中获取空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！