Thursday, March 19, 2015

CASE Expressions

There are two types of CASE expressions: simple and searched. The terminology is a little confusing for me. Here's how each type is used.


The simple CASE expression
  CASE @var
     WHEN 'some literal' -- comparing some value to the value next to CASE
       THEN 'YES'
    WHEN 'some other literal'
      THEN 'NO'
    ELSE 'MAYBE'
END

The searched CASE expression
  CASE
    WHEN @var = 'some literal' -- any Boolean
       THEN 'YES'
    WHEN @var = 'some other literal'
      THEN 'NO'
    ELSE 'MAYBE'
  END

The simple CASE expression
  • Allows only a Boolean expression.
  • Evaluates each WHEN statement in order
  • Returns the results of the first WHEN statement that results in TRUE 
  • If none of the WHEN statements is TRUE, the ELSE clause is used (if it is there)
  • If none of the WHEN statements is TRUE and there is no ELSE clause, returns NULL
The searched CASE expression
  • Evaluates each WHEN statement in order.
  • Returns the results of the first WHEN statement that results in TRUE 
  • If none of the WHEN statements is TRUE, the ELSE clause is used (if it is there)
  • If none of the WHEN statements is TRUE and there is no ELSE clause, returns NULL
From MSDN:  CASE (Transact-SQL)

No comments:

Post a Comment