The CASE expression in SELECT statement in SQL Server 2012 – part 1

The CASE expression is used in SELECT, UPDATE, DELETE statement
and in WHERE, IN, ORDER BY and HAVING clause.

The CASE expression may have two formats:
– simple
– searched

The simple expression has input expression or name of column, it is syntax for it:

CASE input 
     WHEN when_exression THEN result
      ...
     [ ELSE default_result ] 
END 

But searched expression has not any value or expression after CASE word,it is syntax for it:

CASE
     WHEN boolean_expression THEN result
     ... 
     [ ELSE default_result ] 
END

The ELSE is optional in CASE expression.

The first example will be present data from AdventureWorks2012 database from Person table.
We select data of people their lastname begin with Va letters.

USE AdventureWorks2012;
GO
SELECT [Title], [FirstName], [LastName]
       ,YEAR([ModifiedDate])
FROM [AdventureWorks2012].[Person].[Person]
WHERE [Title] is NOT NULL 
AND [LastName] LIKE 'Va%'

ss1

In second example we use CASE simple expression for YEAR. If value the year will be 2005 the result will be ‘old data’, and if this value will be 2006 – the reult will be ‘new data’.
It is several people with 2007 year modiefied. In this place the NULL value will be place.

USE AdventureWorks2012;
GO
SELECT [Title], [FirstName], [LastName]
       ,YEAR([ModifiedDate]) as year,
CASE
WHEN YEAR([ModifiedDate])=2005 THEN 'old_data'
WHEN YEAR([ModifiedDate])=2006 THEN 'new_data'
END as type_data
FROM [AdventureWorks2012].[Person].[Person]
WHERE [Title] is NOT NULL 
AND [LastName] LIKE 'Va%'

ss2

In CASE statement You may not use alias of column in this expression. The YEAR([ModifiedDate]) is good use, but year alias return error.
In places when value from input expression don’t pass to the when expressions, the NULL is return. You may set default value for this situation: use ELSE clause with default value.

USE AdventureWorks2012;
GO
SELECT [Title], [FirstName], [LastName]
       ,YEAR([ModifiedDate]) as year,
CASE
WHEN YEAR([ModifiedDate])=2005 THEN 'old_data'
WHEN YEAR([ModifiedDate])=2006 THEN 'new_data'
ELSE 'other data'
END as type_data
FROM [AdventureWorks2012].[Person].[Person]
WHERE [Title] is NOT NULL 
AND [LastName] LIKE 'Va%';

ss3

And what with searched CASE expression?
The searched CASE expression has after CASE word input expression.
The next example get data of people from Person table.
For searched expression and with ELSE clause:

USE AdventureWorks2012;
GO
SELECT [Title], [FirstName], [LastName]
       ,YEAR([ModifiedDate]) as year,
CASE YEAR([ModifiedDate])
WHEN 2005 THEN 'old_data'
WHEN 2006 THEN 'new_data'
ELSE 'other data'
END as type_data
FROM [AdventureWorks2012].[Person].[Person]
WHERE [Title] is NOT NULL 
AND [LastName] LIKE 'Va%';

ss3The next post will be about use CASE expression in other statements.

Leave a Reply

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.