Transact-SQL local variable with case argument

Question

Given this simple example:

DECLARE @aux INT
SET @aux = NULL

SELECT
CASE WHEN @aux = NULL THEN 'null' ELSE 'not null' END AS ETest,
CASE WHEN @aux <> NULL THEN 'not null' ELSE 'null' END AS ITest;

I expected the result: ETEST null, ITEST NULL, apparently this is wrong, and I need a hint to find where my logic is not working.

Answer 1

You can not use both operators <>and =to check values null. You should use IS NULLor IS NOT NULLas below

DECLARE @aux INT
SET @aux = NULL

SELECT
CASE WHEN @aux IS NULL THEN 'null' ELSE 'not null' END AS ETest,
CASE WHEN @aux IS NOT NULL THEN 'not null' ELSE 'null' END AS ITest;