Reputation: 19
I am trying to write a stored procedure in SQL 2008 R2 and am having major issues. The query behind it should pull first name, last name, balance, & case type. Problem is, some people (i.e., results) will have the same name. So I added a parameter @DOB to weed them down. If there are several people with the same name, the user should be able to enter the date of birth and only get that person back. Problem is...I can't get it to work. Here's my query - warning I use aliases I apologize if its confusing to read:
SELECT cs.last_name ,
cs.first_name ,
cs.dob,
cb.assessment_balance_due ,
cb.case_number,
ct.case_type_title
FROM vw_customer_service cs
INNER JOIN vw_case_balance cb ON cs.case_ID = cb.case_ID
INNER JOIN [case] on cs.case_ID = [case].case_ID
INNER JOIN cd_case_type ct ON [case].case_type_ID = ct.case_type_ID
WHERE **--(cs.dob = case when @dob is null then cs.dob else @dob end )** and
cb.assessment_balance_due > 0
AND cs.last_name = @LastName
AND cs.first_name = @FirstName
AND party_type_ID = 28 --defendant
AND (@dob IS NULL)
GROUP BY
cs.case_ID, cs.last_name, cs.first_name , cb.Assessment_Balance_Due,DOB, cb.case_number, ct.case_type_title
This will only return data if the user puts in the DOB, but I want that to just be an option. How do I filter a result set based on one parameter OR not filter at all if the parameter is left off? Thanks in advance!
Upvotes: 1
Views: 109
Reputation: 7402
Try this...
SELECT cs.last_name , cs.first_name , cs.dob, cb.assessment_balance_due , cb.case_number, ct.case_type_title
from vw_customer_service cs
INNER JOIN vw_case_balance cb on cs.case_ID = cb.case_ID
INNER JOIN [case] on cs.case_ID = [case].case_ID
INNER JOIN cd_case_type ct on [case].case_type_ID = ct.case_type_ID
where (@dob is null or cs.dob=@dob)
and cb.assessment_balance_due > 0
and cs.last_name = @LastName
and cs.first_name = @FirstName
and party_type_ID = 28 --defendant
--and (@dob is null)
group by cs.case_ID, cs.last_name, cs.first_name , cb.Assessment_Balance_Due,DOB, cb.case_number, ct.case_type_title
Upvotes: 1
Reputation: 121037
You can do it like this:
where (@dob is null or cs.dob = @dob) ...
Upvotes: 1