Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem with evaluating Null in Coalesce function

Posted on 2012-03-28
3
Medium Priority
?
386 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 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

718 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