4

I came across a somewhat weird behavior with data type conversions when using ISNULL. Look at this:

PRINT CASE WHEN ISNULL('', 0) = 0 THEN 'true' ELSE 'false' END
PRINT CASE WHEN ISNULL('', 0) = '' THEN 'true' ELSE 'false' END
PRINT CASE WHEN ISNULL(NULL, 0) = '' THEN 'true' ELSE 'false' END

All of these expressions evaluate to true. But when I declare an nvarchar variable and set it to NULL, the following happens:

DECLARE @charType nvarchar; SET @charType = NULL;
PRINT CASE WHEN ISNULL(@charType, 0) = '' THEN 'true' ELSE 'false' END

This should also evaluate to true, but it evaluates to false. Why?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Agent_K
  • 815
  • 8
  • 12

2 Answers2

2

Here ISNULL('', 0) returns '' and ISNULL(NULL, 0) returns 0

Data Type Precedence ...

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned.

As int is having more Precedence over char, '' will be converted to int.

select cast('' as int) ==> 0

Hence in the first case all '' will be converted to 0, so the query will be reduced to 

PRINT CASE WHEN 0 = 0 THEN 'true' ELSE 'false' END
PRINT CASE WHEN '' = '' THEN 'true' ELSE 'false' END
PRINT CASE WHEN 0 = 0 THEN 'true' ELSE 'false' END

Hence it print true

ISNULL ...

Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int

But in the second case as @charType is null, ISNULL will cast the value 0 to nvarchar then the query becomes

So 

PRINT CASE WHEN '0' = '' THEN 'true' ELSE 'false' END

Hence it print false

Praveen
  • 8,945
  • 4
  • 31
  • 49
  • Nicely explained. Thumbs up! – shadow Jan 06 '16 at 14:21
  • I didn't come across the explanation of ISNULL conversion behavior and this of course is the explanation - zero as replacement value is converted to '0'. That answered the question. Thanks, Praveen. – Agent_K Jan 06 '16 at 16:19
  • What made me post this question is the fact, that the general rule about data type precedence is not followed between check expression and replacement value in ISNULL expressions. I had a wrong assumption about that. Actually, the type of the check expression always has precedence (which is clear to me now). – Agent_K Jan 06 '16 at 16:35
0

Try this:

PRINT CASE WHEN CAST(ISNULL('', 0) AS nvarchar) = 0 THEN 'true' ELSE 'false' END
PRINT CASE WHEN CAST(ISNULL('', 0) AS nvarchar) = '' THEN 'true' ELSE 'false' END
PRINT CASE WHEN CAST(ISNULL(NULL, 0) AS nvarchar) = '' THEN 'true' ELSE 'false' END

As you can see SQL knows it needs to cast to nvarchar when @charType is set to a nvarchar type.

What are you trying to achieve?

Tom
  • 747
  • 5
  • 16
  • I came across several usages of ISNULL in existing code recently, which led me to understand fully and in-depth what's going on here. So I'm trying to achieve nothing except understand the code others wrote. – Agent_K Jan 06 '16 at 16:27