Solved

Problem with evaluating Null in Coalesce function

Posted on 2012-03-28
3
371 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now