Writing IF and ELSE conditions in SQL
You can use the IF statement to test conditions in SQL - this blog explains how, and also explains why you might prefer to use CASE WHEN instead.

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

PRINT

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:

PRINT

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.

This blog has 0 threads Add post