Using an IIF statement in a where clause

Question

I am trying to add multiple criteria to a Where clause in SQL Server 2014 using the following code and I am getting a syntax error.

I have tried a case statement but cannot get that to work based on the examples on this site.

Where  
iif(ss.DATAAREAID = 'USMF',
 (ss.ITEMGROUPID like 'S%' and  ss.ITEMGROUPID  not like 'SMS%'),
(ss.ITEMGROUPID like 'SW%' and  ss.ITEMGROUPID  like 'SS%')

I am sure it is a quick solution, but any help would be appreciated.

Answer 1

Don't use conditional logic. Just use boolean expressions:

Where (ss.DATAAREAID = 'USMF', and ss.ITEMGROUPID like 'S%' and ss.ITEMGROUPID  not like 'SMS%') or
      (ss.DATAAREAID <> 'USMF' and ss.ITEMGROUPID like 'SW%' and ss.ITEMGROUPID  like 'SS%')

Note: iif() is a SQL Server function, but it was introduced for backwards compatibility to MS Access. You should use ANSI-standard case expressions instead.

Your version doesn't work because SQL Server does not treat the result of a boolean expression as a valid value. You would need to do something like this:

where (case when . . . and . . . then 1
            else 0
       end) = 1

The above does not take NULL values into account. That condition is easily added if needed.