CODEWITHSUNDEEP
sqlsql-server-2008parameters
eab
eab

Reputation: 19

sql query if parameter is null

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

Answers (2)

Dave C
Dave C

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

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 121037

You can do it like this:

where (@dob is null or cs.dob = @dob) ...

Upvotes: 1

Related Questions