SQL Server 2012 IIF not recognized

Question

I am using SQL Server 2012 and have been trying to use the IIF function, but I'm getting the following error,

Msg 195, Level 15, State 10, Line 1
'iif' is not a recognized built-in function name.

Even with something as simple as

 SELECT IIF(5 > 4, 'true', 'false') 

it fails. In fact, that exact statement gives an "Incorrect syntax near '>'." error

Thanks in advance!

Answer 1

Is there any chance you might be using the Management Studio 2012, but you're connecting to an older version of the SQL Server engine?

Run SELECT @@Version and show us the output - this will show you what engine version you're connected to.

In my case, I get:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

What do you get?!?

IIF only works if you're connected to a 2012 (v11.0) engine version!