sql query if parameter is null

Question

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!

The proposed solutions work, but they might not scale very well if the tables get large and you have many different combinations of parameters. I call this the kitchen sink phenomenon. — Aaron Bertrand
– Aaron Bertrand, Commented Oct 9, 2014 at 18:32
I watched your video - great info. But, this report will always be run by defendant name, so hopefully Kitchen Sink won't apply here! — eab
– eab, Commented Oct 15, 2014 at 1:21

Klaus Byskov Pedersen · Accepted Answer · 2014-10-09 18:29:03Z

1

You can do it like this:

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

answered Oct 9, 2014 at 18:29

Klaus Byskov Pedersen

122k31 gold badges192 silver badges223 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave C · Accepted Answer · 2014-10-09 18:29:34Z

1

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

answered Oct 9, 2014 at 18:29

Dave C

7,4821 gold badge21 silver badges30 bronze badges

1 Comment

eab Over a year ago

Wow, you guys are amazing! Thank you so much for the super quick answer, will try this right now. Thanks again!!

Collectives™ on Stack Overflow

sql query if parameter is null

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related