So I have an SSRS report that has 5 parameters. Three of the parameters are set - with AND in the SQL Where clause:
WHERE LossDate between @FromDate and @ToDate
AND co.FileName like '%' + @Phrase + '%'
I have 2 other parameters that I need to add to the query and they are both optional
Here is my broken query:
SELECT c.LossDate, co.CorrespondenceID, c.ClaimNumber,
co.[FileName], co.Title, co.EntryDate, i.InsuredID, i.InsuredName1 as ClientName,
U1.LastName+', '+U1.FirstName as EnteredByName,
U2.LastName+', '+U2.FirstName ExaminerName,
AddressBook.LastFirstName as ClaimantName
FROM Correspondence co (NOLOCK)
JOIN Claimant cl (NOLOCK) ON co.ClaimantID = cl.ClaimantID
JOIN Claim c (NOLOCK) ON cl.ClaimID = c.ClaimID
JOIN Policy p (NOLOCK) ON c.PolicyID = p.PolicyID
JOIN Insured i (NOLOCK) ON p.InsuredID = i.InsuredID
LEFT OUTER JOIN CorrespondenceStatus cs (NOLOCK) ON co.StatusID = cs.StatusID
LEFT OUTER JOIN Users U1 (NOLOCK) ON co.EnteredBy = U1.UserName
LEFT OUTER JOIN Users U2 (NOLOCK) ON c.ExaminerCode = U2.UserName
LEFT OUTER JOIN AddressBook on cl.AddressBookID = AddressBook.AddressBookID
WHERE LossDate between @FromDate and @ToDate
AND co.FileName like '%' + @Phrase + '%'
AND (ClientName in(@ClientName) OR @ClientName IS NULL)
AND (ClaimNumber = @ClaimNumber OR @ClaimNumber IS NULL)
@ClientName is a list box where they can select one or all values - with no default value
@ClaimNumberis a text parameter where they can enter a single value
The issue with them both being AND in the Where clause is if a user enters a Client and then enters a ClaimNumber that doesn't match, the report won't work.
Do I need to use a Case statement in the Where clause?