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

That is an unusual requirement. I believe the approach I would take is something like this.

DECLARE @SourceID INT

set @SourceID = null;

select sourceid,otherfields
from @ttesting
where isnull(@SourceID,0)=0
   or not exists(
                select 'x' from @tTesting 
                where SourceID = @SourceID)
        )
union
select sourceid,otherfields
from @ttesting
where SourceID=@SourceID

Let's set up some test data:

declare @tTesting as table (
    SourceID   int 
   ,otherfields char(1)
);

insert into @tTesting values (null,'a');
insert into @tTesting values (2,'b');
insert into @tTesting values (1,'c');

The 1st select in the union handles cases where the @SourceID is null, zero, or invalid. The second select in the union deals with the scenario where the @SourceID matches a source id in the table.

--Test Case #1 
--Source ID Exists
DECLARE @SourceID INT
set @SourceID = 0;
...

Returns

sourceid	otherfields
2	b

--Test Case #2 
--Source ID Zero
DECLARE @SourceID INT
set @SourceID = 0;
...

Returns

sourceid	otherfields
NULL	a
1	b
2	c

--Test Case #3
--Source ID invalid
DECLARE @SourceID INT
set @SourceID = 3;
...

Returns

sourceid	otherfields
NULL	a
1	b
2	c