Click here to Skip to main content
14,770,737 members
Please Sign up or sign in to vote.
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:
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
Updated 24-Jan-17 2:03am

Instead of case where, try this:
WHERE (@myparameter = 'Y' AND VaccinationDate IS NOT NULL)
OR (@myparameter = 'N' AND VaccinationDate IS NULL)
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'?
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.


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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900