UMG Developer (8/3/2010)
So it would be perfectly reasonable for a single COALESCE statement to return 5 or more different types depending on the inputs. (Well maybe not reasonable, but certainly possible.)
Actually, with Coalesce it comes down to the expression with the highest data type precedence being returned, thus exactly 1 predictable type will be returned. Consider the following which results in an error.
DECLARE @STR int
SET @STR=NULL
SELECT COALESCE(@STR,'GIVEN STRING IS NULL',CAST(1 AS DATETIME))
Conversion failed when converting date and/or time from character string.
In terms of precedence, Datetime > Int > String, thus the int can be cast into a DateTime, but the String cannot, hence the error.