[RESOLVED] CASE Statment in SQL WHERE Clause

[Mark Gambo]

I have the following SQL String:

Declare @UNIT NVARCHAR(50)
Declare @CASENUM NVARCHAR(50)
 
SET @UNIT = Null /*'Sales'*/
Set @CASENUM = Null /*'C0020'*/
 
SELECT INC.Unit, INC.CASENUM
FROM INCIDENTS INC INNER JOIN CASES CS ON INC.CASENUM = CS.CASENUM 
WHERE 
       (CASE @CASENUM 
        WHEN Null THEN 1
        WHEN @CASENUM THEN (CASE WHEN INC.CASENUM = @CASENUM THEN 1 ELSE 0 END) END) = 1
    OR
       (CASE @UNIT 
        WHEN Null THEN 1
        WHEN @UNIT THEN (CASE WHEN INC.UNIT = @UNIT THEN 1 ELSE 0 END) END) = 1
ORDER BY INC.UNIT

This code works fine as long as I supply at least one of the Variables, but What I want to do if both variables are null I want all of the records returned. When both variables are omitted this SQL String returns no records.

Any ideas?