CODEWITHSUNDEEP
sql-servert-sqlcase
Vaibhav Jain
Vaibhav Jain

Reputation: 34407

CASE Expression with T-SQL

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.

Upvotes: 2

Views: 211

Answers (3)

Jeffrey L Whitledge
Jeffrey L Whitledge

Reputation: 59463

There are two kinds of CASE statements: "simple" and "searched". In your code, you are combining these two kinds of statements, and that is giving you incorrect results.

Simple:

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

Searched:

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

In your original query, it is doing a simple version comparing the value of SR.[ContainerId] to the value of SR.[ContainerId] IS NULL. The result of that comparison will always be false, so the else condition was always selected.

Upvotes: 1

Widor
Widor

Reputation: 13275

Nearly! You were mixing the two different syntax forms: 

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

Upvotes: 3

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171411

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

Upvotes: 5

Related Questions