Now the returned type is based on the first input, and therefore SQL
Server doesn’t try to convert the character string. So you get the output 'abc'.
As for untyped NULL literal inputs, as I mentioned, if all inputs to
COALESCE are the untyped NULL literal, you get an error. To demonstrate this, run the following code:
SELECT COALESCE(NULL, NULL);
You get the following error:
Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be an
expression that is not the NULL constant.
As long as there’s a typed NULL in the input, the code succeeds:
SELECT COALESCE(CAST(NULL AS INT), NULL);
You get a NULL typed as an integer as the output.
The ISNULL function does allow both inputs to be untyped NULLs,
in which case it returns a NULL typed as an integer as the output. To
demonstrate this, run the following code:
SELECT ISNULL(NULL, NULL);
You get a NULL typed as an integer as the output. To prove this, run
the following code:
SELECT ISNULL(NULL, NULL) AS col1
INTO dbo.T1;
