--ISNULL returns the data type of the 1st parameter, hence the error
SELECT ISNULL(CAST(NULL AS INT),'A')
--WHY DOES THIS WORK? IS IT THAT DATA PRCEDENCE COMES INTO PLAY HERE.
SELECT ISNULL(CAST(NULL AS VARCHAR(10)),1)
No, the numeric "1" is cast to VARCHAR(10) to match the first parameter. With Precedence they would both be converted to INT - you can see the later if you use COALESCE instead of ISNULL because ISNULL always takes the data type of the first parameter, whereas COALESCE uses precedence to decide what result data type to use.
SELECT ISNULL(CAST(NULL AS VARCHAR(10)),1) AS TestIsNull
, COALESCE(CAST(NULL AS VARCHAR(10)),1) AS TestCoalesce
INTO TEMP_20160407
SELECT * FROM TEMP_20160407
PRINT '***** Review the TYPE column for the two columns:'
EXEC sp_help TEMP_20160407
GO
DROP TABLE TEMP_20160407
GO
TestIsNull TestCoalesce
---------- ------------
1 1
***** Review the TYPE column for the two columns:
Column_name Type Length Prec Scale
------------ ------- ------ ----- -----
TestIsNull varchar 10
TestCoalesce int 4 10 0
Thanks Kristen for the explanation.
I thought even the following would not work simply because the data type is different:
SELECT ISNULL(CAST(NULL AS INT),'1') --WORKS
SELECT ISNULL(CAST(NULL AS INT),'A') -- DOES NOT WORK
So this means the data type of the 2nd param is "converted" into the data type of the 1st param provided it can be "converted", as we saw above 'A' cannot be converted to int but '1' even though a string is converted to int 1.
Learnt something new, thank you.
Not exactly! It is only converted if the first parameter IS NULL. If in testing your first parameter is always NOT NULL then it will hide a bug 
SELECT ISNULL(CAST(1 AS INT),'A') -- No error
SELECT ISNULL(CAST(NULL AS INT),'A') -- Now you get that error :(
Agree with you, I meant it can be converted with the assumption that first param is NULL.
and your following point is interesting as well:
SELECT ISNULL(CAST(1 AS INT),'A') -- No error
Annoying that ... unfortunately COALESCE does the same thing ...
We use COALESCE exclusively because we prefer its Data Type Precedence method, and the fact that it can take 3+ parameters - i.e. we prefer to use the same function, rather than use a different one only when we want 3+ parameters.
ISNULL is faster in the instance where the first parameter is declared as NOT NULL as the optimiser will remove ISNULL altogether as redundant, which doesn't happen with COALESCE ... but ... if you are Belt and Braces checking NOT NULL data for NULL absolute performance is probably not the biggest issue!
Ok Thanks.