Coalesce function not working when column data is null

Question

1.00/5 (1 vote)

See more:

HI guys,
I wrote a stored proc to get the customer details based on User email or Phone number. i used coalesce to wrote this sp. i got the results when User email and phone number columns data exists. but, i am unable to get the results when column data is null.
Please help me to fix this.
Thanks

What I have tried:

SQL

select UserId,UserEmail,Code,PhoneNo,Name,UserImage,U.CreatedDateTime,A.Country,U.UserImageUrl,
 from Users U ,Address A where
[UserEmail]=coalesce('email@gmail.com',UserEmail)  and
[PhoneNo]=coalesce(null,PhoneNo) and 
A.Id=U.AddressId 
and A.CreatedUserId=U.UserId

Posted 10-Jul-17 18:35pm

.net333

Updated 12-Jul-17 19:33pm

v2

Add a Solution

Comments

Richard Deeming 11-Jul-17 11:42am

NB: You should avoid using the (deprecated) old-style joins, and instead use the ANSI-92 join syntax:

from Users U INNER JOIN Address A ON A.Id = U.AddressId And A.CreatedUserId = U.UserId

.net333 13-Jul-17 1:12am

Thank you Richard. i will follow the ANSI-92 Join

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

.net333 · Accepted Answer · 2017-07-12T19:33:00

Solution 3

I solved this by using

COALESCE([Useremail],'')=coalesce(@Email,[UserEmail],'') and
COALESCE([PhoneNo],'')=coalesce(@PhoneNumber,[PhoneNo],'')

It handles the null values with coalesce function

Posted 12-Jul-17 19:33pm

.net333

Comments

Maciej Los 13-Jul-17 2:02am

+5 as an incentive to work/study.

Maciej Los · Accepted Answer · 2017-07-10T21:14:00

Solution 2

At this moment coalesce returns always email@gmail.com, as it is non-null.

So, replace:

[UserEmail]=coalesce('email@gmail.com',UserEmail)

with:

[UserEmail]=coalesce(UserEmail,'email@gmail.com')

BTW:
I don't understand the usage of: [PhoneNo]=coalesce(null,PhoneNo). This means: replace null with first non-null value of PhoneNo.
Check the documentation as an0ther1[^] mentioned in his anwer.

Posted 10-Jul-17 21:14pm

Maciej Los

Updated 10-Jul-17 21:16pm

v2

Comments

.net333 13-Jul-17 1:15am

Thanks Maciej Los. I need to get the User Details with Email or Phone Number. That's why i used coalesce function.

.net333 13-Jul-17 1:32am

I solved this by using
COALESCE([Useremail],'')=coalesce(@Email,[UserEmail],'') and
COALESCE([PhoneNo],'')=coalesce(@PhoneNumber,[PhoneNo],'').

It handles the null values with coalesce function

Maciej Los 13-Jul-17 2:00am

Great!
Cheers,
Maciej

an0ther1 · Accepted Answer · 2017-07-10T19:33:00

Solution 1

Coalesce returns the first non-null value from your expression - refer;
MSDN - COALESCE (Transact-SQL)[^]
Perhaps you should be using ISNULL - refer;
ISNULL (Transact-SQL) | Microsoft Docs[^]

Kind Regards

Posted 10-Jul-17 19:33pm

an0ther1

Comments

Maciej Los 11-Jul-17 3:15am

A4! Refer to documentation is always good option, but you have to explain what OP is doing wrong.

an0ther1 11-Jul-17 22:00pm

Thank Maciej

.net333 13-Jul-17 1:16am

Thanks an0ther1. i will refer the documents

an0ther1 13-Jul-17 1:28am

No problems buddy.
I read your reply to Maciej - if you need to get either the phone number or email you can use coalesce as follows
[ContactPhoneEmail] = COALESCE(UserEMail, PhoneNumber, 'email@gmail.com')

Cheers

.net333 13-Jul-17 1:30am

I solved this by using
COALESCE([Useremail],'')=coalesce(@Email,[UserEmail],'') and
COALESCE([PhoneNo],'')=coalesce(@PhoneNumber,[PhoneNo],'').

It handles the null values with coalesce function