COALESCExy COALESCEyx ISNULLxy
---------- ---------- --------
1234567890 1234567890 123
ISNULLyx
----------
1234567890
Notice that with COALESCE, regardless of which input is specified first,
the type of the output is VARCHAR( 10)—the one with the higher precedence. However, with ISNULL, the type of the output is determined
by the first input. So when the first input is of a VARCHAR( 3) data type
(the expression aliased as ISNULLxy), the output is VARCHAR( 3). As a
result, the returned value that originated in the input @y is truncated
after three characters.
In the last example, all inputs are character strings, just of different
lengths. What if the inputs are of different data type families? The
same rules apply. For example, the following COALESCE expression
accepts a character string as the first input and an integer as a second
input:
SELECT COALESCE('abc', 1);
An integer has a higher data type precedence than a character string;
therefore, SQL Server tries to convert the value 'abc' to an integer and
naturally fails. This code generates the following conversion error:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value
'abc' to data type int.
Try a similar expression, using ISNULL instead:
www.sqlmag.com
sql server Pro / may 2013
