IIF(...) not a recognized built-in function
Solution 1
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
Solution 2
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.
...
Solution 3
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).
Solution 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
![Danny Beckett](https://i.stack.imgur.com/SU0xt.jpg?s=256&g=1)
Danny Beckett
I'm a PHP/JS developer & C#/XAML programmer from Liverpool . I lived in Amsterdam for a couple of years, and Oslo for a little while too. OS windows-server centos GUI c# .net wpf visual-studio Web php html5 css3 javascript jquery SQL mysql mssql t-sql oracle pl-sql sybase-asa sqlanywhere HTTPd apache iis
Updated on February 03, 2022Comments
-
Danny Beckett over 2 years
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 nameIs
IIF()
only compatible with a later version?Is there an alternate function I can use?