Error when combining IIF and 'Is Null'

Question

I need to combine three columns representing address lines into one column, in order to compose the entire address here. All three columns can contain a NULL value. I thought the way to go about this is as follows:

SELECT IIF((add1.AddressLine1 Is Null), '', add1.AddressLine1 + CHAR(13)) + 
       IIF((add1.AddressLine2 Is Null), '', add1.AddressLine2 + CHAR(13)) + 
       add1.AddressLine3  As EntireAddress
FROM T_Address add1

However, both instances of "Is" have a red squiggly underlining, indicating "Incorrect syntax near 'Is'." errors. What am I doing wrong/How can I achieve what I want? I use SSMS 2012.

Thanx!

I can't replicate this problem; `DECLARE @a int; SELECT IIF(@a IS NULL,1,0);` works fine. — Thom A, Apr 24 '18 at 08:45
@Larnu, This does not work fine with me. A. I need to put @a IS NULL in brackets, otherwise I get "The IIF function requires 3 argument(s)." B. I'm back to my initial error when I apply the brackets. — Cooz, Apr 24 '18 at 09:53
@Cooz SSMS isn't a version of SQL, it's an application. What version of SQL Server are you using? (You're answer would be akin to asking someone "What version of Windows do you have?" and they replied with "I'm using Microsoft Office 2016."). — Thom A, Apr 24 '18 at 09:55
@Cooz - what does this query return `SELECT @@VERSION` Also, when you are running your query what exactly is the error that your are getting, the 'red squiggly' won't help us in helping you out. — Abhishek, Apr 24 '18 at 10:38
@Larnu - Of course! Sorry! That would be SQL Server 2008 - and that's probably the source of the error. I need a more recent version, I guess. — Cooz, Apr 24 '18 at 11:42
@Abhishek SQL Server 2008. The error I get is "Incorrect syntax near 'Is'." — Cooz, Apr 24 '18 at 11:43
@Cooz - that was what I had suspected earlier just wanted to verify. Glad the problem has been solved — Abhishek, Apr 24 '18 at 12:52

score 5 · Accepted Answer · answered Apr 24 '18 at 11:53

IIF (Transact-SQL) was introduced in SQL Server 2012. The problem is you're trying to use a function that doesn't exist, not that IS NULL is an issue.

IIF is a shorthand way for writing a CASE expression. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression is true, and the false_value is returned if the Boolean expression is false or unknown. true_value and false_value can be of any type. The same rules that apply to the CASE expression for Boolean expressions, null handling, and return types also apply to IIF. For more information, see CASE (Transact-SQL).

Thus, instead you can do:

SELECT CASE WHEN add1.AddressLine1 IS NULL THEN '' ELSE add1.AddressLine1 + CHAR(13) END + 
       CASE WHEN add1.AddressLine2 IS NULL THEN '' ELSE add1.AddressLine2 + CHAR(13) END + 
       add1.AddressLine3  As EntireAddress --Note, if AddressLine3 has a value of NULL then NULL will be returned here
FROM T_Address add1;

However, why not simply use COALESCE or ISNULL and NULLIF?

SELECT NULLIF(ISNULL(add1.AddressLine1 + CHAR(13),'') +
              ISNULL(add1.AddressLine2 + CHAR(13),'') +
              ISNULL(AddressLine3,''),'') AS EntireAddress
FROM T_Address add1;

This is much more succinct.

Yes, thank you @Larnu. The db is installed on SQL Server 2012 now and your suggestions all work fine here. Much appreciated! — Cooz, Apr 24 '18 at 12:10

johnDanger · Answer 2 · 2019-11-15T23:42:51.947

This is a nice workaround for trying to do IIF(boolean checking for Null, yes_value, no_value) in general (although Im not sure it allows conditionally adding characters, e.g. + CHAR(13), as in the OPs problem). I found this as an answer to a question on checking for empty strings:

ISNULL(NULLIF(no_value, Null), yes_value)

The no_value will be checked if it is Null
If it is Null then it is replaced with the yes_value
If it is not Null it will be returned

Error when combining IIF and 'Is Null'

2 Answers2