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

i think this is what you want...

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');

DECLARE @SourceID INT
set @SourceID = 0;
select sourceid,otherfields
from @ttesting
where

/*
** if the @SourceID is zero or invalid return
** rows where SourceID is null
*/

   ( 
        isnull(@SourceID,0)=0
   OR
        (
                 isnull(@SourceID,0) > 0
        AND
                NOT EXISTS(
                                select 'x' from @tTesting 
                                where SourceID = @SourceID
                        )
        )
   )
   AND
   (
    sourceid IS NULL
   )
UNION
select sourceid,otherfields
from @ttesting
where 

/*
** returns rows where SourceID matches 
** passed variable
*/

SourceID=@SourceID