Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

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
0
chtullu135
Asked:
chtullu135
  • 2
1 Solution
 
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
 
chtullu135Author 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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now