How to use condition in T-SQL

Question

I'm trying to use a select case in order to either use a where condition or not in a sql query. But it does not work and I can use some help how to solve this if possible

DECLARE @CategoryID Int
SET @CategoryID = 0
SELECT * FROM SomeTable 
CASE WHEN @CategoryID = 0 THEN 
ELSE
WHERE        (dbo.SomeTable.ID = @CategoryID)
END

So if I pass 0 to the @CategoryID parameter I should not filter at all, otherwise I want to filter. Can this be done?

Answer 1

Rephrase your logic to remove the CASE expression:

SELECT *
FROM SomeTable
WHERE @CategoryID IN (0, ID);

This works, because when @CategoryID = 0, the WHERE clause is always true, otherwise it would be true when ID = @CategoryID.

Your current attempt is a common problem, stemming from not using a CASE expression as it is intended. The predicate of a CASE expression (i.e. what follows the logic after THEN and ELSE) has to be a literal value, not another logical expression. In this case, we don't even need CASE.