Caveats of "CASE" statement testing for "NULL" values.

"CASE" statement syntax seems pretty easy.

There is only two different cases of usage:

Here are couple of samples

;WITH SampleData AS (     SELECT 'AZ' AS TestColumn1, 1 AS TestColumn2     UNION SELECT 'BL', 2 UNION SELECT 'CK', 3     ) SELECT TestColumn1,     CASE         WHEN LEFT(TestColumn1,1) = 'A' THEN TestColumn2*10         WHEN LEFT(TestColumn1,1) = 'B' THEN TestColumn2*100         WHEN LEFT(TestColumn1,1) = 'C' THEN TestColumn2*1000    END AS TestMultiplication FROM SampleData;

;WITH SampleData AS (     SELECT 'AZ' AS TestColumn1, 1 AS TestColumn2     UNION SELECT 'BL', 2 UNION SELECT 'CK', 3     ) SELECT TestColumn1,     CASE LEFT(TestColumn1,1)         WHEN 'A' THEN TestColumn2*10         WHEN 'B' THEN TestColumn2*100         WHEN 'C' THEN TestColumn2*1000    END AS TestMultiplication FROM SampleData;

These two scripts produce the same result, but the second one is much smaler.
Moreover, if instead of "LEFT" function you are using user defined function (which BTW is extremely bad practice for large data sets) then that function will be executed individually for every "WHEN" clause.

Is it the reason to use shorter version?

Usually it is not. In the second version you loose ability to search by ranges and able to search for only one parameter/field.

And that is not everything.

In certain situations it even can produce wrong results:

;WITH SampleData AS (SELECT NULL AS TestColumn UNION SELECT 1) SELECT TestColumn     , CASE             WHEN TestColumn IS NULL THEN 'It is Null'         ELSE 'It is Not Null'END AS Regular_Case     , CASE TestColumn             WHEN NULL THEN 'It is Null'         ELSE 'It is Not Null'END AS Search_Case FROM SampleData;

As you can see, the "Search" did not recognized "NULL" value

Why that was happen?

When SQL Server compares any value to a NULL it returns NULL, which immediately go to the "ELSE" portion of the "CASE" statement.

By knowing that behavior you will avoid these kind of collisions in your CASE statements.