NULLIF

Question

Post reply

NULLIF

Bhuvnesh

SSC Guru

Points: 59351
More actions
March 29, 2010 at 1:40 am

#99126

I got some weird( may be i am not aware of this fact ) results in case of NULLIF
declare @send_email_flag smallint
set @send_email_flag=1
select nullif(@send_email_flag, '')
Result :1 ( Fine result)
declare @send_email_flag smallint
set @send_email_flag=0
select nullif(@send_email_flag, '')
Result : NULL
But Expected Result is: 0.Any idea ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

nullif:Returns a null value if the two specified expressions are equal.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Hi Vyas, can you please explain it with my sql code.?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Why it is treating 0 and '' (Blank) same ? in select nullif( 0, '')

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

BrainDonor SSCoach Points: 19254 More actions · Answer 4

Bhuvnesh (3/29/2010)
Why it is treating 0 and '' (Blank) same ? in select nullif( 0, '')

Because you have a number and a character field to compare, SQL will convert the second field to the same type as the first field.

If you Cast an empty string to an integer you will get zero:

SELECT CAST(' ' AS int), so it is comparing zero to zero. When both fields match it will return NULL.

BrainDonor.

Steve Hall
Linkedin
Blog Site

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 5

NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.

BrainDonor SSCoach Points: 19254 More actions · Answer 6

BrainDonor (3/29/2010)
Bhuvnesh (3/29/2010)
Why it is treating 0 and '' (Blank) same ? in select nullif( 0, '')
Because you have a number and a character field to compare, SQL will convert the second field to the same type as the first field.
If you Cast an empty string to an integer you will get zero:
SELECT CAST(' ' AS int), so it is comparing zero to zero. When both fields match it will return NULL.
BrainDonor.

Ignore me - it's early and I've just typed rubbish.:doze:

Steve Hall
Linkedin
Blog Site

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 7

This is nonsensical, there is no BLANK value for a smallint. There is NULL or a Value.

Internally , SQLserver is equating 0 to ''. TBH im surprised that an error is not thrown as you are mixing types.

Clear Sky SQL
My Blog[/url]

vidya_pande SSCertifiable Points: 5275 More actions · Answer 8

This is really wonderful finding

Recommend you to change the code to

declare @send_email_flag char(1)

set @send_email_flag='1'

select nullif(@send_email_flag, '')

go

declare @send_email_flag char(1)

set @send_email_flag='0'

select nullif(@send_email_flag, '')

go

--or

declare @send_email_flag smallint

set @send_email_flag=1

select nullif(@send_email_flag, null)

go

declare @send_email_flag smallint

set @send_email_flag=0

select nullif(@send_email_flag, null)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

Thanks to all for your replies, i got the point.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)