Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Expand Minimize

COALESCE (SQL Server Compact)

Returns the first nonnull expression among its arguments.

COALESCE ( expression [ ,...n ] ) 


An expression of any data type.


A placeholder indicating that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible to the same type.

Returns the highest precedence type from the set of types in expression.

In this example, a table includes three columns with information about an employee's yearly wage: hourly_wage, salary, and commission. However, an employee receives only one of these pay types.

To determine the total amount paid to all employees, use the COALESCE function to receive only the nonnull value found in the hourly_wage, salary, and commission columns.

SELECT CONVERT(money, COALESCE(null, 5, null, 40 * 52, 65, null)) AS "First nonnull value"
© 2015 Microsoft