how do you deal with case null?

CASE @test WHEN NULL THEN ...........

[columnname] =
    CASE
        WHEN [columnname] IS NULL THEN 'It's NULL'
        ELSE 'It's not NULL'
    END

The expression implies:

CASE WHEN @test = NULL THEN...

And you probably know value = NULL evaluates to UNKNOWN which in turn (in most cases) evaluates to
FALSE. Here's how to do:

CASE WHEN @test IS NULL THEN...

So, use a searched case instead of a simple case.

Show quoteHide quote

CASE
   WHEN @test IS NULL THEN...

HTH
Vern

Show quoteHide quote

for future reference...

null is not a value.  and it's a grave mistake to use it as such in the
database.  try to avoid inserting null in the database at all cost;
certain cases are unavoidable, however.

since it's not a value, you can't compare against it. ie. x = null.

can't delete a row from primary table - is there an SP for this?
Ranking Rows
T-SQL
Convert(datetime, '07/11/48') Returns Year 2048!!
UPDLOCK blocking readers