How to use IF...THEN logic in SQL server
Posted by: AJ Welch
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
'Middle-earth'
ELSE
'Earth'
END AS locale,
books.*
FROM
booksBefore 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
booksTherefore, 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
'Middle-earth'
ELSE
'Earth'
END AS localeTo begin, we of initialize the CASE statement then specify under which conditions (WHEN) our CASE statement should evaluate a result. In this example, we’re examining the books.title and 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'
ENDFinally, 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 function. IIF is a shorthand method for performing an IF...ELSE/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
booksWith an 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 CASE and IIF get the same job done, but if given the choice, IIF will generally be much simpler to use.