CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
Jason Renaldo
Jason Renaldo

Reputation: 2822

WHERE filter on different values using a CASE

This seems to be a popular scenario yet I cannot seem to implement a solution from the previous answers on the topic.

I need to do something like this in SQL Server 2012:

DECLARE @conditionVarChar AS VARCHAR(MAX) = 'Use ham'
DECLARE @food AS VARCHAR(MAX) = 'ham'
DECLARE @somethingElse VARCHAR(MAX) = 'somethingelse'

SELECT *
FROM tempTable
--Here is my issue
WHERE CASE WHEN @conditionVarChar LIKE '%ham%' --If there is 'ham' in this varchar
           THEN @food = tempTable.Food --Then filter from this
           ELSE @somethingElse = tempTable.SomethingElse --Else, filter on something completely different
           END

Thanks

Upvotes: 2

Views: 52

Answers (1)

Hart CO
Hart CO

Reputation: 34784

You don't really need a CASE statement to do this, you can just filter on sets of criteria and use OR:

WHERE (@conditionVarChar LIKE '%ham%' AND @food = tempTable.Food)
  OR  (@somethingElse = tempTable.SomethingElse)

Depending on actual criteria it may be more practical to use dynamic sql to build criteria lists.

Upvotes: 4

Related Questions