CODEWITHSUNDEEP
sqlsql-server-2014isnull
nickc
nickc

Reputation: 49

isnull function in WHERE clause

I am attempting to fix an issue in a stored procedure and have come across an issue that is vexing me.

Basically, isnull works as expected for one record in T0 but not in another, both where T0.FatherCard are NULL. I cannot see why.

SELECT *
FROM OINV T0
WHERE ISNULL(T0.FatherCard, T0.CardCode) = 'C0189'

Returns a full row of data as expected. 

SELECT *
FROM OINV T0
WHERE ISNULL(T0.FatherCard, T0.CardCode) = 'C0817'

Returns nothing. I am expecting a full row of data.

In both cases, FatherCard = NULL

CardCode    FatherCard  Table
------------------------------
C0189       NULL        OINV
C0817       NULL        OINV

FatherCard and CardCode are both of the same type (nvarchar) and length (50).

If I remove the ISNULL function and simply select WHERE T0.CardCode = C0817 then it works as expected.

Is it possible T0.FatherCard is actually not NULL for the purposes of the ISNULL evaluation, and is returning some other value?

Upvotes: 0

Views: 2449

Answers (2)

Disillusioned
Disillusioned

Reputation: 14832

There are 2 possibilities.

  1. FatherCard may have the string value "NULL" and not actually be NULL.
  2. You could have extraneous spaces at the end of C0817 I.e. 'C0817 '

To check use:

SELECT  '[' + CardCode + ']', ISNULL(FatherCard, 'Yes is NULL')
FROM    OINV
WHERE   RTRIM(CardCode) = 'C0817'

Upvotes: 2

Eralper
Eralper

Reputation: 6612

Please use

where columnname is null

Upvotes: 0

Related Questions