.png)
COALESCE
SQL Server 2000
Returns the first nonnull expression among its arguments.
Syntax
COALESCE ( expression [ ,...n ] )
Arguments
- expression
- Is an expression of any data type.
- n
- Is 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.
Return Types
Returns the highest precedence type from the set of types in expression.
Examples
In this example, the wages table is shown to include three columns with information about an employee's yearly wage: hourly_wage, salary, and commission. However, an employee receives only one type of pay. To determine the total amount paid to all employees, use the COALESCE function to receive only the nonnull value found in hourly_wage, salary, and commission.
CREATE TABLE wages ( emp_id int identity, hourly_wage numeric NULL, salary numeric NULL, commission numeric NULL, num_sales int NULL ) INSERT wages (hourly_wage, salary, commission, num_sales) VALUES(10.00, NULL, NULL, NULL) INSERT wages (hourly_wage, salary, commission, num_sales) VALUES(20.00, NULL, NULL, NULL) INSERT wages (hourly_wage, salary, commission, num_sales) VALUES(30.00, NULL, NULL, NULL) INSERT wages (hourly_wage, salary, commission, num_sales) VALUES(40.00, NULL, NULL, NULL) INSERT wages (hourly_wage, salary, commission, num_sales) VALUES(NULL, 10000.00, NULL, NULL) INSERT wages (hourly_wage, salary, commission, num_sales) VALUES(NULL, 20000.00, NULL, NULL) INSERT wages (hourly_wage, salary, commission, num_sales) VALUES(NULL, 30000.00, NULL, NULL) INSERT wages (hourly_wage, salary, commission, num_sales) VALUES(NULL, 40000.00, NULL, NULL) INSERT wages (hourly_wage, salary, commission, num_sales) VALUES(NULL, NULL, 15000, 3) INSERT wages (hourly_wage, salary, commission, num_sales) VALUES(NULL, NULL, 25000, 2) INSERT wages (hourly_wage, salary, commission, num_sales) VALUES(NULL, NULL, 20000, 6) INSERT wages (hourly_wage, salary, commission, num_sales) VALUES(NULL, NULL, 14000, 4) SELECT CONVERT(money, COALESCE(hourly_wage * 40 * 52, salary,commission * num_sales)) AS "Total Salary" FROM wages
