CODEWITHSUNDEEP
sql-servert-sqltype-conversionisnull
Agent_K
Agent_K

Reputation: 814

ISNULL and Implicit datatype conversions in TSQL

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?

Upvotes: 4

Views: 1429

Answers (2)

Praveen
Praveen

Reputation: 9335

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

Upvotes: 2

Tom
Tom

Reputation: 747

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?

Upvotes: 0

Related Questions