Conditional search on date field

Question

0.00/5 (No votes)

See more:

I have a stored procedure that needs to return all pets that either have had all their vaccinations or have not had all their vaccinations.

Here is my stored procedure:

SQL

SELECT PetName, OwnerLName, OwnerFName, VaccinationDate  
  FROM [dbo].[myVetsOffice]
  WHERE (@PetName IS NULL OR PetName LIKE  '%' + @PetName + '%')
    AND (@OwnerLName IS NULL OR OwnerLName LIKE  '%' + @OwnerLName + '%')
    AND (@OwnerFName IS NULL OR OwnerFName LIKE  '%' + @OwnerFName + '%')
ORDER BY OwnerLname, OwnerFname

I have a Y/N parameter that, if it is Y, I need to add VaccinationDate IS NOT NULL else if the Y/N parameter is N, then include a clause where the VaccinationDate is null.

How do I include this in my select? I tried including a AND CASE WHEN @myparameter = 'Y' THEN VaccinationDate IS NOT NULL but that is not working. How else can I include this last bit of logic?

What I have tried:

CASE statement, google search, msdn site, blogs, stack overflow

Posted 23-Jan-17 18:55pm

Member 10379103

Updated 24-Jan-17 1:03am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 9831003 · Answer 1 · 2017-01-24T01:03:00

Solution 2

For then part of case it will accept only specific value or column value i.e it will not going to accept column is not null or column is null .

In then part you have to make another case statement and write you logic accordingly.

Thanks

Posted 24-Jan-17 1:03am

Member 9831003

Peter Leow · Answer 2 · 2017-01-23T20:23:00

Solution 1

Instead of case where, try this:

WHERE (@myparameter = 'Y' AND VaccinationDate IS NOT NULL)
OR (@myparameter = 'N' AND VaccinationDate IS NULL)

Posted 23-Jan-17 20:23pm

Peter Leow

Comments

Member 10379103 24-Jan-17 12:12pm

Peter - thanks for your tip. It helped. I have one slight modification - if @myparameter is 'Y', the doctor wants to see all vaccination records - just some of them when @myparameter is 'N'. I have an IF statement that displays different versions on the query based on @myparameter.

Is there a more efficient way to display all fields when @myparameter is 'Y'?