Wednesday, 28 August 2013

SQL Using Case in Where clause for null values

SQL Using Case in Where clause for null values

I have a SQL query that I am trying to incorporate the possibility of null
responses in my selections.
Ultimately, this will end up in a SSRS report.
This query works fine, but any null values in p.ReferralReason will always
be returned. I would like the nulls to not be returned if the value of
@Reason is anything but '%':
DECLARE @Reason varchar(100)
SET @Reason = 'Lost To Care'
SELECT p.Person_ID, P.Person_Name, p.ReferralReason
FROM VIEW_Patient p
WHERE
p.ReferralReason like '%' + @Reason + '%'
I would like to incorporate all reasons with the @Reason = '%' If @Reason
is set to '%', I would like to include the null values, but I do not want
to include the null values if @Reason is set to anything else.
This is what I have tried, but it does not work:
DECLARE @Reason varchar(100)
SET @Reason = '%'
SELECT p.Person_ID, P.Person_Name, p.ReferralReason
FROM VIEW_Patient p
WHERE
case
when @Reason = '%' then (p.ReferralReason like '%' + @Reason +
'%' or p.ReferralReason is null)
else p.ReferralReason like '%' + @Reason + '%'
end
MS SQL Server 2008 R2.

No comments:

Post a Comment