Problem with evaluating Null in Coalesce function

Hello,

I've written the following stored procedure that uses the coalasce function to create a dynamic where clause.  However no data is returned when @Review 4 is evaluated.  I checked the underlying table and I found that the Review4 had a null value for that record.
So I think what is happening is when I execute EXEC dbo.spSetGFPRecordSource with no input parameters,  and hit "AND PR.Review4 = COALESCE(@Review4,PR.Review4)" , PR.Review is used because @Review is null and that the expression in effect becomes "AND PR.Review = Null" .  Is that correct?  I need help with how to handle this Null

ALTER PROC dbo.spSetGFPRecordSource
@GFP AS VARCHAR(MAX) = NULL,
@WorkStream AS VARCHAR(Max) = NULL,
@Review1 AS VARCHAR(MAX) = NULL,
@Review2 AS VARCHAR(MAX) = NULL,
@Review3 AS VARCHAR(MAX) = NULL,
@Review4 AS VARCHAR(MAX) = NULL,
@Review5 AS VARCHAR(MAX) = NULL,
@Review6 AS VARCHAR(MAX) = NULL,
@Review7 AS VARCHAR(MAX) = NULL,
@Review8 AS VARCHAR(MAX) = NULL
AS

select @Review4

SELECT      PR.USI, PR.GFP, PR.WorkStream,
            PR.Review1 , PR.Status1,  
            PR.Review2 , PR.Status2,  
            PR.Review3 , PR.Status3,  
            PR.Review4 , PR.Status4,  
            PR.Review5 , PR.Status5,  
        PR.Review6 , PR.Status6,  
            PR.Review7 , PR.Status7,
            PR.Review8 , PR.Status8  
            FROM dbo.vwPivotedReviews AS PR
            WHERE      PR.GFP = COALESCE(@GFP,PR.GFP)
            AND            PR.Workstream = COALESCE(@Workstream,PR.WorkStream)                         
            AND            PR.Review1 = COALESCE(@Review1,PR.Review1)
            AND            PR.Review2 = COALESCE(@Review2,PR.Review2)
            AND            PR.Review3 = COALESCE(@Review3,PR.Review3)
            AND            PR.Review4 = COALESCE(@Review4,PR.Review4)
--            AND            PR.Review5 = COALESCE(@Review5,PR.Review5)
--            AND            PR.Review6 = COALESCE(@Review6,PR.Review6)  
--            AND            PR.Review7 = COALESCE(@Review7,PR.Review7)  
--            AND            PR.Review8 = COALESCE(@Review8,PR.Review8)
            ORDER BY PR.USI
Juan VelasquezAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rajeevnandanmishraCommented:
Hi,
Yes you are correct. We need to handle this NULL.
I think you need to change your where clause to use COALESCE in both sides like below:
AND      COALESCE(PR.Review1,'') = COALESCE(@Review1,PR.Review1,'')
AND      COALESCE(PR.Review2,'') = COALESCE(@Review2,PR.Review2,'')
AND      COALESCE(PR.Review3,'') = COALESCE(@Review3,PR.Review3,'')
AND      COALESCE(PR.Review4,'') = COALESCE(@Review4,PR.Review4,'')

Doing this will ensure that if the Review is Null then it is evaluated to blank.
Give it a check.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Juan VelasquezAuthor Commented:
Hello rajeevnandanmishra

Thanks for the assist.  That took care of the problem.  From what I can see, what your code is doing is in effect setting an empty string as the value if both the variable and the field value evaluate to null.  Is that correct?
0
rajeevnandanmishraCommented:
You are welcome.
Correct. The COALESCE function must have at least one non-null parameter. Searching from left to right, it returns the first non-null value.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.