Using CASE statements to control program logic

Using CASE statements to control program logic

You can't do this, but there is a way round it sometimes.  To explain what I mean by this, consider this example query to print out a message according to how many short films there are in a table:

IF EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes < 60)

PRINT 'There are some really short films'

ELSE

IF EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes < 90)

PRINT 'There are some shortish films'

ELSE

PRINT 'No short films found'

This uses subqueries, but hopefully it's reasonably clear what's going on.  This nested IF condition is hard to read, and will get harder as we add more possible conditions.  It would be much easier to read and write using CASE, but the following won't work:

CASE

WHEN EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes < 60)

THEN PRINT 'There are some really short films'

WHEN EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes < 90)

THEN PRINT 'There are some shortish films'

ELSE

PRINT 'No short films found'

END

This is because you can't use CASE to control program flow.  However, you can use it to calculate expressions, leading to the following work-round:

-- variable to hold message to display

DECLARE @Message varchar(MAX)

-- work out what should be displayed

SET @Message =

CASE

WHEN EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes < 60)

THEN 'There are some really short films'

WHEN EXISTS(SELECT * FROM tblFilm WHERE FilmRunTimeMinutes < 90)

THEN 'There are some shortish films'

ELSE

'No short films found'

END

-- print this out

PRINT @Message