There's more... Try the performance if this (remember the execution plan will be saved for this query in proc).
SELECT DISTINCT a.*
FROM (
SELECT Case30.Case_no#,
Case30.Last_name,
Case30.[First Name],
Case30.Determination,
Case30.Sender,
Case30.[Case Type],
Case30.Date,
Case30.[Qwik Due],
Case30.[Qwik sent date],
Case30.[Final due],
Case30.[Final sent date],
Case30.[on hold code],
tblFileNames.reportid,
tblFileNames.type,
userviewed.viewid
FROM tblFileNames
Left Outer Join userviewed ON tblFileNames.reportid = userviewed.reportid
Right Outer Join Case30 ON tblFileNames.case_id = Case30.Case_no#
WHERE (@param1 IS NULL OR (@param1 IS NOT NULL AND Case_no# = @param1))
AND (@param2 IS NULL OR (@param2 IS NOT NULL AND last_name = @param2))
AND (@param3 IS NULL OR (@param3 IS NOT NULL AND [First name] = @param3))
AND (@param4 IS NULL OR (@param4 IS NOT NULL AND Determination = @param4))
AND (@param5 IS NULL OR (@param5 IS NOT NULL AND sender = @param5))
AND (@param6 IS NULL OR (@param6 IS NOT NULL AND [Case Type] = @param6))
AND (@param7 IS NULL OR (@param7 IS NOT NULL AND Date = @param7))
AND (@param8 IS NULL OR (@param8 IS NOT NULL AND [Qwik Due] = @param8))
AND (@param9 IS NULL OR (@param9 IS NOT NULL AND [Qwik sent date] = @param9))
AND (@param10 IS NULL OR (@param10 IS NOT NULL AND [Final due] = @param10))
AND (@param11 IS NULL OR (@param11 IS NOT NULL AND [Final sent date] = @param11))
AND (@param12 IS NULL OR (@param12 IS NOT NULL AND [on hold code] = @param12))
UNION
SELECT DISTINCT CASEOLDR.Case_no#,
CASEOLDR.Last_name,
CASEOLDR.[First Name],
CASEOLDR.Determination,
CASEOLDR.Sender,
CASEOLDR.[Case Type],
CASEOLDR.Date,
CASEOLDR.[Qwik Due],
CASEOLDR.[Qwik sent date],
CASEOLDR.[Final due],
CASEOLDR.[Final sent date],
CASEOLDR.[on hold code],
tblFileNames.reportid,
tblFileNames.type,
userviewed.viewid
FROM tblFileNames
Left Outer Join userviewed ON tblFileNames.reportid = userviewed.reportid
Right Outer Join CASEOLDR ON CASEOLDR.Case_no# = tblFileNames.case_id
WHERE (@param1 IS NULL OR (@param1 IS NOT NULL AND Case_no# = @param1))
AND (@param2 IS NULL OR (@param2 IS NOT NULL AND last_name = @param2))
AND (@param3 IS NULL OR (@param3 IS NOT NULL AND [First name] = @param3))
AND (@param4 IS NULL OR (@param4 IS NOT NULL AND Determination = @param4))
AND (@param5 IS NULL OR (@param5 IS NOT NULL AND sender = @param5))
AND (@param6 IS NULL OR (@param6 IS NOT NULL AND [Case Type] = @param6))
AND (@param7 IS NULL OR (@param7 IS NOT NULL AND Date = @param7))
AND (@param8 IS NULL OR (@param8 IS NOT NULL AND [Qwik Due] = @param8))
AND (@param9 IS NULL OR (@param9 IS NOT NULL AND [Qwik sent date] = @param9))
AND (@param10 IS NULL OR (@param10 IS NOT NULL AND [Final due] = @param10))
AND (@param11 IS NULL OR (@param11 IS NOT NULL AND [Final sent date] = @param11))
AND (@param12 IS NULL OR (@param12 IS NOT NULL AND [on hold code] = @param12))
) a
Far away is close at hand in the images of elsewhere.
Anon.