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!

Answer 1

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.