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)

Wednesday, February 4, 2015

Alter Schema

Database objects and types can be moved between schemas using the ALTER SCHEMA statement.  I have never had occasion to use this statement - that's why it's on this blog.  If I blog it, I usually remember it.

To move a database object TableA from schemaOne to schemaTwo,
ALTER SCHEMA schemaTwo TRANSFER schemaOne.TableA

To move a database type TypeA from schemaOne to schemaTwo
ALTER SCHEMA schemaTwo TRANSFER TYPE::schemaOne.TypeA

This can be found on MSDN Alter Schema (Transact-SQL)