Geek Answers Handbook

Using ISNULL where the condition does not return records with a NULL field

Table:

ID     AppType     AppSubType   Factor
1   SC  CD      1.0000000000
2   SC  CD      2.0000000000
3   SC  NULL    3.0000000000
4   SC  NULL    4.0000000000

Query:

declare @ast varchar(10)

set @ast = null

select *
from tbl
where AppType = 'SC' and AppSubType = ISNULL(@ast, AppSubType)

Result:

ID  AppType AppSubType  Factor
1   SC  CD  1.0000000000
2   SC  CD  2.0000000000

Question:

Should this query not return all 4 records, not just the first 2?

+4
source share
3 answers

Obviously, @ast is null and Isnull will exchange null with another value, so you should not expect @ast to be null. If your AppSubType is NULL, the result becomes null, but AppSubType=nulldoes not mean that it AppSubType is nullis true. Since null is not a value, therefore, it cannot work with equal. for your expected result, this code will work.

declare @ast varchar(10)

set @ast = null

select *
from tbl
where AppType = 'SC' and (AppSubType = ISNULL(@ast, AppSubType) Or AppSubType is null)
+4
source

case where, :

declare @ast varchar(10)

set @ast = null

select *
from tbl
where AppType = 'SC' and 1=
      case when isnull(@ast ,'') = '' and isnull(AppSubType ,'') = '' then 1
           when AppSubType = ISNULL(@ast, AppSubType) then 1 
           else 0
      end
0

, , ISNULL .

the very first expression in the isnull function is the value of the column or the expression of some result.

ISNULL Details investigated

The code looks like a search function. If the value is not set, replace it with everything that is in the database, and if you are given to pull only those records that match.

0
source

More articles:


All Articles