Alternative to Binary Case Statements

When outputting data through a procedure it’s a common use case to add fields which have been calculated from existing data. One way to achieve this is using the CASE statement, however we don’t always need that level of complexity, particularly when we’re looking for a binary result such as ‘Yes’ / ‘No’.

An alternative to using a CASE statement for these short binary decisions is with the use of an Inline IF function – or IIF – as a shorthand.

Example

Let’s start by considering a simple decision below from the AdventureWorks database:

SELECT p.BusinessEntityID,
	AllowPromotion = 
		CASE
		WHEN p.EmailPromotion > 0 THEN 'Yes'
		ELSE 'No' END
FROM Person.Person p;

This is the scenario we’re looking for; a binary decision where the CASE statement is only returning one of two possible results. This is essentially an IF statement so a perfect candidate for the IIF function.

The function is similar to a regular IF statement however the expression is just the first of three parameters. If the expression evaluates to true then the second parameter is returned, and if it evaluates to false. Essentially living up to it’s name as an Inline IF statement:

IF Parameter 1 Expression IS TRUE
THEN Return Parameter 2
ELSE Return Parameter 3

With that said let’s take a look at the example above translated to use the IIF function instead:

SELECT p.BusinessEntityID,
	AllowPromotion =
		IIF(p.EmailPromotion > 0,
		'Yes',
		'No')
FROM Person.Person p;

Both of the above examples are wrapped for readability but you should be able to see the IIF example is more concise of the two and will achieve exactly the same result.

If you want to know more about the function there’s a short read up on the online documentation.

Wrap up

That’s it really. There’s really not a lot to this function but I was interested to find it in the SQL engine. I’d only previously seen this in the Expression Builder within Reporting Services and thought it was specific to that.

This function is similar to the way we have ISNULL when we don’t require the ability to have multiple parameters like COALESCE. If there are situations where you anticipate the IIF statements being expanded with a need for additional cases then it makes sense to future proof and dive straight in with a CASE statement. Where we’re making a binary decision this is a great option for keeping code succinct.

Is this a function you’ve been aware of and used before or do you prefer your CASE statements to cover more use cases?