T-SQL unexpected behavior in isnull function

Question

When i run this query i expect the result will be 'false'

IF isnull(0,'') = '' 
      select 'true' 

else 
     select 'false'

But sql Server tells me 'true', why?

Because isnull(0,'') returns the integer 0, and if you check your second operand also as integer : select cast('' as integer), it also returns 0. You shouldn't use different types, this ends with this kind of conversion issues. — Marc Guillot
– Marc Guillot, Commented Dec 1, 2016 at 8:30
Why do you use ISNULL() like this? Using such code in a WHERE or SELECT statement to convert null values to something else would force a full table scan. Using this in a SELECT statement isn't possible — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 1, 2016 at 8:31
@MarcGuillot But read this which says that casting an empty string to numeric results in error. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 1, 2016 at 8:31
@TimBiegeleisen that's also what I expected, but I have checked and select cast('' as integer) returns 0. — Marc Guillot
– Marc Guillot, Commented Dec 1, 2016 at 8:33
@TimBiegeleisen that's an extremely old and closed documentation issue. It doesn't say that the cast is invalid, just that the documentation omits this. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 1, 2016 at 8:39

Panagiotis Kanavos · Accepted Answer · 2016-12-01 08:40:00Z

2

In this case ISNULL(0,'') returns an integer. SQL Server will cast the second argument to an integer too, ie 0. So 0=0, hence the result is TRUE. Comparing directly to 0 would also return true:

IF 0 = '' 
      select 'true' 

else 
     select 'false'

Using ISNULL and NULL like this is unusual. An ISNULL(someColumn='') function in a WHERE clause would prevent the optimizer from using any indexes that covered someColumn thus forcing a scan instead of an index seek.

Using IF statements in SELECT is impossible. Even in CASE statements, it's better to explicitly check for NULL than apply such transformations.

edited Dec 1, 2016 at 8:40

answered Dec 1, 2016 at 8:32

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

star Over a year ago

ok, thx for explanation. i tried "select cast ('' as int)" and it returns 0

Panagiotis Kanavos Over a year ago

@Star why are you doing this anyway? What is the actual problem you want to solve by this IF ISNULL construct? Most likely, there are easier and faster ways to do it

Tim Biegeleisen · Accepted Answer · 2016-12-01 08:53:39Z

2

For your case, when compare two values, the '' will be converted to int first. The following:

SELECT CONVERT(INT, '')

returns 0, so 0=0 is true

If you want treat 0 as NULL, you can use NULLIF:

DECLARE @i INT = 0
IF NULLIF(ISNULL(@i, ''), 0) = ''
     SELECT 'true'
ELSE
     SELECT 'false'

This would return 'false'

edited Dec 1, 2016 at 8:53

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

answered Dec 1, 2016 at 8:43

Nolan Shang

2,3281 gold badge14 silver badges10 bronze badges

Comments

Husen · Accepted Answer · 2016-12-01 08:27:18Z

0

ISNULL are identical when there are just two values (i.e. NULL and 0) so, it will be true in IF condition and select 'true' will be printed.

answered Dec 1, 2016 at 8:27

Husen

1,58510 silver badges14 bronze badges

Collectives™ on Stack Overflow

T-SQL unexpected behavior in isnull function

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related