nickc nickc - 1 month ago 17
SQL Question

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?

Answer

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'