Search code examples
ssms-2014

IIF Statement in SQL Server Management Studio 2014

I’m using SQL Server Management Studio 2014 and trying to run code with an IIF statement.

The syntax is correct as far as I can see but I keep getting an error

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '='.

The code is below

SELECT 
    [Name], [Street], [City],
    IIF([Country] = 'UK', 'UK', 'Overseas') AS Country
FROM 
    [dbo].TblAddress
Solution

  • The syntax is correct.

    It now depends on which version of SQL Server you have rather than which version of SQL Server Management Studio.

    For example running 

    SELECT 
    [Name], [Street], [City],
    IIF([Country] = 'UK','UK','Overseas') AS Country
FROM 
    [dbo].TblAddress

    on SQL Server 2012 or higher would work (SQL Server 2014 in the below example)

    SQL Server 2014

    But on SQL Server 2008 or lower it returns: (SQL Server 2008 R2 is Version 10.50...)

    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near '='.

    enter image description here

    This is because IIF is only available in SQL Server 2012 onwards

    https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-iif-transact-sql

    The version of SQL Server Management Studio doesn't matter.