42

I am trying to use IIF() in a select statement. The boolean expression checks to see if a fields value is equal to an empty string. The syntax is like so:

SELECT IIF(field = '','ONe action','Another')

I am getting the error "syntax error near ="

I tried a simple test:

SELECT IIF(2 > 1, 'yes','no')

and I am getting "syntax errror near >"

This is leading me to believe that IIF is not working at all.

I am using SQL SERVER 2008 R2, is there something that needs to be configured to allow IIF() to work? Is there something about the syntax that I am missing? My test is simple as can be and I still get the syntax error.

Any help would be appreciated. Thanks much!

Richard
  • 29,854
  • 11
  • 77
  • 120
TheMethod
  • 2,893
  • 9
  • 41
  • 72

4 Answers4

79

As noted, IIF is a SQL2012 feature.

Replace your IIF with a CASE ( Which is what SQL 2012 would do anyway )

 SELECT CASE field WHEN '' THEN 'ONe action' ELSE 'Another' END
podiluska
  • 50,950
  • 7
  • 98
  • 104
32

IFF is available starting from SQL Server 2012. So use 'Case' instead.

If you are looking for a more compact form (function instead of case) in SQL Server 2008 you can use:

isnull((select 'yes' where 2 > 1),'no')

instead of:

SELECT IIF(2 > 1, 'yes','no')

Then your statement will be:

SELECT isnull((select 'yes' where 2 > 1),'no')

akjoshi
  • 15,374
  • 13
  • 103
  • 121
Alexander
  • 7,484
  • 4
  • 51
  • 65
14

IIF was introduced in SQL Server 2012, so that is why it does not work in SQL Server 2008 or SQL Server 2008 R2.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
2
DECLARE @a int=45

DECLARE @b int=40

SELECT   
      CASE 
         WHEN @A>@B THEN 'True'

 ELSE 'FALSE'         
      END

You can use this solution instead of IIF.

Ilyes
  • 14,640
  • 4
  • 29
  • 55