Can we use CASE statement in a where clause? Answer is yes.

The way it works is like this:

SELECT * FROM myTable
WHERE col1 = CASE WHEN col2 < col3 THEN col2
ELSE col3
END

Another example:

I wanna check if a column matches an ID variable, @itemId, in the where clause. If @itemId has a positive value we return the matching rows that have that itemId, otherwise we return all rows. How I do that?

declare @itemId int;

select * from Items
Where
ItemId=CASE When @itemId>0 THEN @itemId ELSE itemId END;