IIF(...) not a recognized built-in function

Question

I'm trying to use this in Microsoft SQL Server 2008 R2:

SET @SomeVar = @SomeOtherVar +
  IIF(@SomeBool, 'value when true', 'value when false')

But I get an error:

IIF(...) is not a recognized built-in function name

Is IIF() only compatible with a later version?

Is there an alternate function I can use?

Most pages of BOL have an "Other Versions" link/drop-down below the title if the item has existed in previous versions. Given the absence on this page, I'd say it's a 2012 feature only. — Damien_The_Unbeliever, Aug 20 '12 at 09:24
possible duplicate of [SQL Server 2008 IIF statement does not seem enabled](http://stackoverflow.com/questions/11540753/sql-server-2008-iif-statement-does-not-seem-enabled) — podiluska, Aug 20 '12 at 10:07
`IIF()` is an MS Access function. The `case` statements described in the answers are the ANSI standard way of doing conditional statements. — Gordon Linoff, Dec 21 '13 at 16:16
Note to dopes like me: Ensure you didn't type `IFF` in place of `IIF`. Two ayes have it. One does not. — ruffin, May 19 '21 at 16:46

score 46 · Accepted Answer · edited May 21 '21 at 18:19

46

IIF comes from SQL 2012. Before then, you can use CASE:

SET @SomeVar = @SomeOtherVar + CASE
 WHEN @SomeBool
 THEN 'value when true'
 ELSE 'value when false'
END

edited May 21 '21 at 18:19

Danny Beckett

20,529
24
107
134

answered Aug 20 '12 at 09:27

Richard

29,854
11
77
120

1

Hi, I am trying to use 'IFF' in SQL 2014, however, I am getting the same error - "IFF' is not a recognized built-in function name." Was it discontinued in SQL 2014...? MSDN exclusively mentions only SQL 2012. ( https://msdn.microsoft.com/en-us/library/hh213574(v=sql.110).aspx) – Koder101 May 08 '16 at 13:47
11

The function is `IIF`, not `IFF`. Otherwise, check the Compatibility Level setting on your database. – Richard May 08 '16 at 19:07
@Richard This gets me every time – xr280xr Apr 05 '22 at 23:17

score 9 · Answer 2 · edited Jun 20 '20 at 09:12

9

What's New in SQL Server 2012, Programmability Enhancements:

SQL Server 2012 introduces 14 new built-in functions. These functions ease the path of migration for information workers by emulating functionality that is found in the expression languages of many desktop applications. However these functions will also be useful to experienced users of SQL Server.

...

IIF (Transact SQL)

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 20 '12 at 09:26

Damien_The_Unbeliever

234,701
27
340
448

score 4 · Answer 3 · answered Aug 20 '12 at 09:25

4

IIF is not valid for SQL Server 2008 R2 and any version before that.

IIF was introduced in SQL Server 2012 (there is no link to previous versions on the documentation page I have linked to).

answered Aug 20 '12 at 09:25

Oded

489,969
99
883
1,009

I've been using IIF in SQL Server for 20+ years. But can't get it to work on SQL Server 2008R2 – Dave Oct 31 '18 at 15:28

score 4 · Answer 4 · answered Dec 23 '13 at 05:09

4

You could also use the standard IF statement if it's outside a select.

E.g.

DECLARE @Answer VARCHAR(3) = 'YES'

IF @Answer = 'Yes'
BEGIN 
--Do Something if true
END
ELSE
-- Do Soemthing if false

answered Dec 23 '13 at 05:09

Will Wainwright

322
5
14

IIF(...) not a recognized built-in function

4 Answers4

Linked