CODEWITHSUNDEEP
sql-serversyntax-erroriif
Cooz
Cooz

Reputation: 93

Error when combining IIF and 'Is Null'

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!

Upvotes: 4

Views: 46442

Answers (3)

Marcello Miorelli
Marcello Miorelli

Reputation: 3698

Just to illustrate the already good previous answers, sometimes I have a check for null and something else altogether, as you can see on the example below.

this script gets you the drop index including an unique index or primary key

check for the IIF:

select [@Command] = CASE WHEN i.is_unique_constraint = 1
                           OR i.is_primary_key = 1
                         THEN  'ALTER TABLE ' +  QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + '.' + QUOTENAME(OBJECT_NAME(t.object_id))  + ' drop constraint ' + QUOTENAME(i.name)
                         ELSE  'DROP INDEX '  +  QUOTENAME(i.name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + '.' + QUOTENAME(OBJECT_NAME(t.object_id)) 
                    END
                                             
       ,[@table_name] = OBJECT_SCHEMA_NAME(t.object_id) + '.' + OBJECT_NAME(t.object_id) 
       ,[Is this index Unique?]=IIF (i.is_unique IS NULL OR i.index_id = 0,NULL, CASE WHEN i.is_unique = 0 THEN 'No' ELSE 'Yes' END )
       ,[Index Description] =  CASE WHEN i.type_desc = 'HEAP' 
                                     THEN 'HEAP' 
                                     ELSE i.type_desc + 
                                            CASE WHEN i.is_unique_constraint = 1 THEN ', UNIQUE CONSTRAINT' ELSE '' END +
                                            CASE WHEN i.is_primary_key = 1       THEN ', PRIMARY KEY' ELSE '' END 
                                END 
       ,i.*
    from sys.tables t 
    inner join sys.indexes i
      on i.object_id = t.object_id  
    where 1=1
      --and t.name = @table_name 
      --and i.type=2 
      --and i.is_unique=1

ORDER BY OBJECT_SCHEMA_NAME(t.object_id) + '.' + OBJECT_NAME(t.object_id) ,I.is_unique

this gives me a list of unique and non unique indexes including the dropping script:

enter image description here

Upvotes: 0

johnDanger
johnDanger

Reputation: 2342

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

Upvotes: 1

Thom A
Thom A

Reputation: 96037

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.

Upvotes: 6

Related Questions