CASE Expression with T-SQL

Question

I am using:

SELECT
  CASE SR.[ContainerId] WHEN SR.[ContainerId] IS NULL
    THEN 0
    ELSE 1
  END AS [IsSampleReceived]
FROM SomeTable SR where SomeCondition

Its not giving me the desired result. IsSampleReceived is always 1. I don't know why, maybe there's some thing wrong in WHEN SR.[ContainerId] IS NULL.

Answer 1

There are two formats of using CASE and you are mixing them together.

The CASE expression has two formats:

The simple CASE expression compares an expression to a set of simple expressions to determine the result.

The searched CASE expression evaluates a set of Boolean expressions to determine the result.

See http://msdn.microsoft.com/en-us/library/ms181765.aspx

Instead, try:

select case 
        when SR.[ContainerId] is null
            then 0
        else 1
        end as [IsSampleReceived]
from SomeTable SR
where SomeCondition