CODEWITHSUNDEEP
sqliif
LiquidDrummer
LiquidDrummer

Reputation: 428

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?

Upvotes: 0

Views: 160

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270553

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)
      )

Upvotes: 1

Related Questions