The CASE expression in SELECT statement in SQL Server 2012
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%'
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%'
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 NUL
L 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%';
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%';
The next post will be about use CASE expression in other statements.