[SOLVED] SQL IIF Statement Inside WHERE

SQL IIF Statement Inside WHERE

I am attempting to return a list of items based on a selection of attributes that may or may not be selected. These attributes are listed in a separate table (ItemAttributeMap) by themselves and are bound to the items table via an Id. The attributes are sent to the api in a comma-separated string. I know that the following code works as long as an attribute is selected:

SELECT * FROM ItemCatalog 
WHERE ItemCatalog.Id IN 
    (SELECT ItemAttributeMap.ItemCatalogId FROM ItemAttributeMap WHERE ItemAttributeMap.AttributeId IN (SELECT * FROM STRING_SPLIT(@Attributes, ',')))

However, since there may not be any attribute filters selected, I must deal with the case where the @Attributes parameter will be an empty string. I have tried the following, but it does not work:

WHERE ItemCatalog.Id IN IIF
(
    LEN(@Attributes) > 0,
    (SELECT ItemAttributeMap.ItemCatalogId FROM ItemAttributeMap WHERE ItemAttributeMap.AttributeId IN (SELECT * FROM STRING_SPLIT(@Attributes, ','))),
    (SELECT ItemCatalog.Id FROM ItemCatalog)
)

Am I going in the completely wrong direction with this?

Solution

Yes, you care completely off. Scalar expressions do not use list that can be used with IN.

Instead, just use boolean logic:

WHERE (LEN(@Attributes) > 0 AND 
       ItemCatalog.Id IN (SELECT ItemAttributeMap.ItemCatalogId FROM ItemAttributeMap WHERE ItemAttributeMap.AttributeId IN (SELECT * FROM STRING_SPLIT(@Attributes, ',')))
      ) OR
      (LEN(@Attributes) = 0
       ItemCatalog.Id IN (SELECT ItemCatalog.Id FROM ItemCatalog)
      )