While going through “Question of the day” from http://www.sqlservercentral.com/, I discovered an issue with the ISNULL function.
If you were to run the query below 1Please ensure you test any script taken from my website on a test/development machine, before running on a production server., you will be surprised to hear, that the result returned is “A”. I am sure you would expect the result to be “ABC”.
SELECT ISNULL ( CAST(NULL AS VARCHAR(1)), CAST('ABC' AS VARCHAR(3)) )
The reason for this, is that both parameters of the ISNULL function should be the same data type. If not, then the data type of the left parameter is used, even if the result is obtained from the right-hand parameter.
To quote the MSDN article (http://msdn.microsoft.com/en-us/library/ms184325.aspx):
ISNULL ( check_expression , replacement_value )
The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.