BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
This blog is part of our online SQL tutorial series. We mainly run training courses in SQL for businesses, as well as courses in Reporting Services, Analysis Services and Integration Services.
Posted by Andy Brown on 29 January 2013
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Conditions in SQL - IF / ELSE
All you programmers out there who have been following this tutorial are probably salivating now at the prospect of getting stuck into some proper programming. Salivate not, because ...
IF conditions are not that commonly used in SQL; if you find yourself littering your SQL with conditions, you're probably doing something wrong.
Notwithstanding the hint above, there is a place for IF statements in SQL. Let's first have a look at the syntax, then consider some examples.
Syntax of a simple IF statement
The way you write an IF statement in SQL is as follows:
-- test if a condition is true
IF (condition true)
DO SOMETHING
To make it clearer what's going on (and to allow you to run a number of different statements if a condition is true), use BEGIN and END as bookmarks:
-- test if a condition is true
IF (condition is true)
BEGIN
DO ONE THING
DO ANOTHER THING
END
My advice would be always to use BEGIN / END, especially if you come from a VB background. Oh, and resist that temptation to put the word THEN after the condition!
Multiple IF conditions using ELSE
Suppose you want to say: if one thing is true, do this; otherwise, do that. For this, you'll need to use the ELSE keyword. The syntax is:
-- test if a condition is true
IF (condition is true)
BEGIN
DO THING A
DO THING B
END
ELSE
BEGIN
DO THING C
DO THING D
DO THING E
END
If you want to test more than two conditions, you're going to have to nest your IF statements. For example:
-- test if any one of 3 conditions is true
IF (condition is true)
BEGIN
DO THING A
END
ELSE
IF (condition 2 is true)
BEGIN
DO THING B
END
ELSE
BEGIN
DO THING C
END
It's probably worth adding that in all my SQL programming career I've never built this complex an IF statement; you should be using CASE statements instead!
The importance of indentation
The following is valid SQL: it will print a suitable message according to the day of the week:
| Day of week | Number | Message |
|---|---|---|
| Saturday, Sunday | 7, 1 | It's the weekend! |
| Friday | 6 | It's Friday ... |
| Any other day | 2 to 5 | It's a weekday ... / Time to work ... |
Here's the SQL without indentation - it's nigh on impossible to read:
-- print different messages according to
-- the day of the week
IF DatePart(weekday,GetDate()) IN (1,7)
PRINT 'It''s the weekend!'
ELSE
IF DatePart(weekday,GetDate()) = 6
PRINT 'It''s Friday ...'
ELSE
BEGIN
PRINT 'It''s a weekday ...'
PRINT 'Time to work ...'
END
If we add proper indentation, everything suddenly becomes much clearer:
-- print different messages according to
-- the day of the week
IF DatePart(weekday,GetDate()) IN (1,7)
PRINT 'It''s the weekend!'
ELSE
IF DatePart(weekday,GetDate()) = 6
PRINT 'It''s Friday ...'
ELSE
BEGIN
PRINT 'It''s a weekday ...'
PRINT 'Time to work ...'
END
OK - having looked at the syntax of the IF statement, let's look at some examples.
Example - using EXISTS to see if records exist
The following query prints out a suitable message if SQL Server can find films which won ten or more Oscars:
-- see if there are any films winning
-- ten or more Oscars
IF EXISTS (
SELECT * FROM tblFilm
WHERE FilmOscarWins >= 10
)
BEGIN
PRINT 'Double-digit Oscar winners found'
END
Example - using variables to compare two counts
The following example creates two variables to hold:
- the number of films lasting 3 hours or more; and
- the number of films winning at least 1 Oscar.
The SQL then compares the two numbers, and prints a suitable message:
-- see if there are more Oscar-winners or long films
DECLARE @LongFilms int
DECLARE @OscarWinners int
-- find number of 3 hour plus films
SET @LongFilms = (
SELECT COUNT(*) FROM tblFilm
WHERE FilmRunTimeMinutes >=180
)
-- find number of films winning Oscars
SET @OscarWinners = (
SELECT COUNT(*) FROM tblFilm
WHERE FilmOscarWins > 0
)
-- show which is more
IF @LongFilms > @OscarWinners
PRINT 'More long films'
ELSE
IF @LongFilms <>
PRINT 'More Oscar winners'
ELSE
PRINT 'Same number of each'
CASE WHEN is nearly always better
If at all possible, use CASE WHEN instead of an IF to test multiple conditions, as it creates SQL which is much easier to read (and write). Here's how you could have written some of the queries above. First, the day of the week example:
-- print different messages according to
-- the day of the week
CASE DatePart(weekday,GetDate())
WHEN 1 THEN 'It''s the weekend!'
WHEN 7 THEN 'It''s the weekend!'
WHEN 6 THEN 'It''s Friday ...'
ELSE 'It''s a weekday ...'
END
You could write the last statement of the long films / Oscar winners query above as:
CASE
WHEN @LongFilms > @OscarWinners THEN 'More long films'
WHEN @LongFilms < @oscarwinners="">THEN 'More Oscar winners'
ELSE 'Same number of each'
END
In short, if you're using an IF statement, then there's a good chance you should be using CASE WHEN instead.