Hi,

Thanks for posting this. I have few clarification on COALESCE result type and i am confused the type conversion.

Consider the case below.

--Case 1

DECLARE @STR int

SET @STR=NULL

SELECT COALESCE(@STR,null,CAST('2010-05-05' AS DATETIME))

Result: 2010-05-05 00:00:00.000

--Case 2

DECLARE @STR2 int

SET @STR2=NULL

SELECT COALESCE(@STR2,null,CAST('ABCD' AS varchar(10)))

Result:

Error: Msg 245, Level 16, State 1, Line 9

Conversion failed when converting the varchar value 'ABCD' to data type int.

--Case 3

DECLARE @STR2 int

SET @STR2=NULL

SELECT COALESCE(@STR2,null,CAST('2222' AS varchar(10)))

Result: 2222

In case1, @STR is int type, but expression 2 in COALESCE is datetime. But i except the output to be 40301 which is the int value for date 2010-05-05 when following rule on case2 and case 3?

How case1 is evaluated?

Also what exactly mean by "1. Data type determination of the resulting expression - ISNULL uses the first parameter type, COALESCE follows the CASE expression rules and returns type of value with highest precedence"

Thanks in advance

Gopi