SQL Server has a unique capability of allowing you to execute real-time programmatic logic on the values within your query. Based on those logical evaluations, you can generate various values as part of the returned data set.
Using the CASE Statement
This is most easily accomplished in all versions of SQL Server using the
CASE statement, which acts as a logical
IF...THEN...ELSE expression and returns various values depending on the result.
In this example below, we want to return an additional
locale column that specifies whether our book takes place in Middle-earth or regular old Earth.
SELECT CASE WHEN books.title = 'The Hobbit' THEN 'Middle-earth' WHEN books.primary_author = 'Tolkien' THEN 'Earth' ELSE 'Earth' END AS locale, books.* FROM books
Before we examine the special
CASE aspect of this statement, let’s temporarily remove the
CASE to notice that this is an extremely simple
SELECT statement on the surface:
SELECT books.* FROM books
Therefore, let’s examine how the
CASE section is structured and what logical behavior we’re performing.
CASE WHEN books.title = 'The Hobbit' THEN 'Middle-earth' WHEN books.primary_author = 'Tolkien' THEN 'Earth' ELSE 'Earth' END AS locale
To begin, we of initialize the
CASE statement then specify under which conditions (
CASE statement should evaluate a result. In this example, we’re examining the
books.primary_author; if either fit our Tolkien-esque theme,
THEN we return the value ‘Middle-earth.’ If neither fields match our search, we instead return the value of ‘Earth.’
To rearrange the logic as a psuedo-code
IF...THEN...ELSE statement, we’re simply asking SQL to evaluate:
IF title == 'The Hobbit' OR primary_author == 'Tolkien' THEN RETURN 'Middle-earth' ELSE RETURN 'Earth' END
Finally, it is critical to remember that a
CASE statement must always be appended at the end with a matching
END statement. In the above example, we’re also renaming the resulting value that is returned to
locale, though that is certainly optional.
Using the IIF Function
If you are using a more modern version of SQL, it is useful to know that SQL Server 2012 introduced the very handy
IIF is a shorthand method for performing an
CASE statement and returning one of two values, depending on the evaluation of the result.
Restructuring our above example to use
IIF is quite simple.
SELECT IIF( books.title = 'The Hobbit' OR books.primary_author = 'Tolkien', 'Middle-earth', 'Earth') AS locale, books.* FROM books
IIF function, we largely replace a lot of the syntactical sugar from the
CASE statement with a few simple comma-seperators to differentiate our arguments.
All told, both
IIF get the same job done, but if given the choice,
IIF will generally be much simpler to use.