CODEWITHSUNDEEP
sqlsql-server
Nilufar Yeasmin
Nilufar Yeasmin

Reputation: 23

In SQL Server How do I use "in" statement in a case condition?

declare @itemList nvarchar(100) = '43,78'
SELECT * FROM ItemMaster
WHERE ItemID = case when @itemList = '0' THEN ItemID ELSE IN (@itemList)

I want all the items from ItemMaster table when @itemList is 0 and when @itemList has a comma separated value, get only those ids. I tried to do this with the code above, but it is not working. How do I do this?

Upvotes: 0

Views: 69

Answers (2)

Thom A
Thom A

Reputation: 95560

Don't. CASE is an expression that returns a Scalar value, not an expression. Using a CASE is the WHERE will effect the SARGability of your query to. Stick to using proper boolean Logic.

In this case, it seems like you're simply after:

WHERE @ItemList = 0
   OR ItemID IN (SELECT item FROM STRING_SPLIT(@ItemList','));

Personally, however, I prefer passing NULL rather than 0 for the "catch all" parameter. Also, if this is a catch-all query, you may well want to add the RECOMPILE option to avoid poor plan caching:

WHERE @ItemList IS NULL
   OR ItemID IN (SELECT item FROM STRING_SPLIT(@ItemList','))
OPTION (RECOMPILE);

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You should be passing the values using a table of some sort (probably a table variable). That would make this simpler. But you can do:

SELECT im.*
FROM ItemMaster
WHERE @ItemList = '' OR   -- a much more reasonable value for "all"
      im.ItemID IN (SELECT value FROM SPLIT_STRING(@itemList, ','));

In older versions of SQL Server, you can use:

SELECT im.*
FROM ItemMaster
WHERE @ItemList = '' OR   -- a much more reasonable value for "all"
      ',' + @itemList + ',' LIKE '%,' + CONVERT(VARCHAR(255), im.ItemID) + ',%';

Upvotes: 1

Related Questions