Can someone explain how the COALESCE function in TSQL works? The syntax is as follows
COALESCE(x, y)
The MSDN document on this function is pretty vague
See Question&Answers more detail:osCan someone explain how the COALESCE function in TSQL works? The syntax is as follows
COALESCE(x, y)
The MSDN document on this function is pretty vague
See Question&Answers more detail:osI'm not sure why you think the documentation is vague.
It simply goes through all the parameters one by one, and returns the first that is NOT NULL
.
COALESCE(NULL, NULL, NULL, 1, 2, 3)
=> 1
COALESCE(1, 2, 3, 4, 5, NULL)
=> 1
COALESCE(NULL, NULL, NULL, 3, 2, NULL)
=> 3
COALESCE(6, 5, 4, 3, 2, NULL)
=> 6
COALESCE(NULL, NULL, NULL, NULL, NULL, NULL)
=> NULL
It accepts pretty much any number of parameters, but they should be the same data-type. (If they're not the same data-type, they get implicitly cast to an appropriate data-type using data-type order of precedence.)
It's like ISNULL()
but for multiple parameters, rather than just two.
It's also ANSI-SQL
, where-as ISNULL()
isn't.