WHERE Clause with CASE WHEN....THEN....ELSE ... END with columns = > and IS NULL conditions

A CASE expression returns a scalar. I am sure you know, that "IS NULL OR > 0" does not resolve to a scalar value.

You could replace it with "SourceID", so you would get

WHERE     SourceID = CASE WHEN @intSourceID > 0 THEN @intSourceID ELSE SourceID END

This will work fine if SourceID does not contain NULL in any row. If it does, those rows would always be filtered out.

If you need to support NULL values (or if you prefer this approach), you could circumvent that by writing the following. It assumes that you will always leave @intSourceID NULL if you don't want to filter (forget about the nonsense of using "either" NULL or 0)

WHERE     (SourceID = @intSourceID  OR  @intSourceID IS NULL)

---

Gert-Jan