Solved

Problem with evaluating Null in Coalesce function

Posted on 2012-03-28
3
373 Views
Last Modified: 2012-03-28
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
0
Comment
Question by:chtullu135
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
rajeevnandanmishra earned 500 total points
ID: 37778758
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
 

Author Comment

by:chtullu135
ID: 37779288
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
 
LVL 9

Expert Comment

by:rajeevnandanmishra
ID: 37779318
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question