My Technical Notes

Wednesday, 6 March 2013

SQL Server CASE Statement

The SQL Server CASE statement can be used in cases when we want to do small if ... then ... logic in our SELECT statement, or use it to assign a variable in the form of a ternary operator obj = boolean_expr ? true_value : false_value.

The following is how one would use it in the case of a SELECT statement:


SELECT TOP 100
      p.Forename
    , p.Surname
    , (CASE
          WHEN p.Gender = 'M' THEN 'Male'
          WHEN p.Gender = 'F' THEN 'Female'
          ELSE 'Transgender' END) GenderDescription
    , p.Username
FROM dbo.People p

The above SQL snippet uses the form of the CASE statement where we are specifying a boolean condition at each level. This is useful for when different column values are being checked in the boolean expressions. If we want a SQL analog of a switch statement then we can use the other form of the CASE statement where we specify the value to be checked and each WHEN will have next to it the value being looked for:


SELECT TOP 100
      p.Forename
    , p.Surname
    , (CASE p.Gender 
          WHEN 'M' THEN 'Male'
          WHEN 'F' THEN 'Female'
          ELSE 'Transgender' END) GenderDescription
    , p.Username
FROM dbo.People p

In most instances the first form will be used.

No comments: