Innovative application of SQLServerCOALESCE () Functions

The COALESCE () function can accept a series of values. If all items in the list are null, only one value is used. Then, it returns the first non-null value. This technique describes two ways to creatively use the COALESCE () function in SQLServer. Here is a simple example: there is a Persons data table, which has three fields FirstNam

The COALESCE () function can accept a series of values. If all items in the list are null, only one value is used. Then, it returns the first non-null value. This technique describes two ways to use COALESCE () functions in SQL Server. Here is a simple example: there is a Persons data table, which has three fields FirstNam

The COALESCE () function can accept a series of values. If all items in the list are null, only one value is used. Then, it returns the first non-null value. This technique describes two ways to use COALESCE () functions in SQL Server.

Here is a simple example: there is a Persons data table with three fields FirstName, MiddleName, and LastName. The table contains the following values:

John A. MacDonald

Franklin D. Roosevelt

Madonna

Cher

Mary Weilage

If you want to use a string to list their full names, the following describes how to use the COALESCE () function to complete this function:

SELECT FirstName + ''' + COALESCE (MiddleName, ''') + ''' + COALESCE (LastName ,'''')

If you do not want each query to be written like this, list A shows how to convert it into A function. In this way, when you need this script (no matter what the actual value of each column is), you can directly call this function and pass three field parameters. In the following example, the parameter I pass to the function is the name of the person, but you can replace it with the field name to get the same result:

SELECT dbo. WholeName (''james '', NULL, ''bond '')

UNION

SELECT dbo. WholeName (''cher '', NULL, NULL)

UNION

SELECT dbo. WholeName (''john'', ''f. '', ''kennedy '')

The test results are as follows:

James Bond

Cher

John F. Kennedy

You may notice a problem. There are two spaces in the name of James Bond. You can correct this problem by modifying the @ result line, as shown below:

SELECT @ Result = LTRIM (@ first + ''' + COALESCE (@ middle, ''') + COALESCE (@ last, '''')

The following is another application of the COALESCE () function. In this example, a payroll is displayed for the employee. The problem is that different employees' wage standards are different (for example, some employees pay by hour, pay by workload once a week or pay by responsibility ). List B is the code for creating a sample table. Below are some sample records, each of which is a type:

1 18.00 40 NULL

2 NULL 4.00 400 NULL

3, NULL, 800.00, NULL

4 NULL 500.00 600

Use the following code to list the total amount paid to employees (regardless of their payment criteria) in the same column ):

SELECT

EmployeeID,

COALESCE (HourlyWage * HoursPerWeek, 0) +

COALESCE (AmountPerPiece * PiecesThisWeek, 0) +

COALESCE (WeeklySalary + CommissionThisWeek, 0) AS Payment

FROM [Coalesce_Demo]. [PayDay]

The result is as follows:

EmployeeID Payment

1 720.00

2 1600.00

3 800.00

4 1100.00

You may need to use this computing method in multiple applications. Although this indicates that the task can be completed, it does not look very beautiful. The following describes how to use a separate summation column to complete this task:

ALTERTABLE Coalesce_Demo.PayDay

ADD Payment

COALESCE (HourlyWage * HoursPerWeek, 0) +

COALESCE (AmountPerPiece * PiecesThisWeek, 0) +

COALESCE (WeeklySalary + CommissionThisWeek, 0)

In this way, you only need to use SELECT * to display the pre-calculated results.

Summary

This article describes some special scenarios and special methods for using COALESCE () functions. In my experience, COALESCE () functions most often appear in a specific content, such as a query or view or stored procedure.

You can put COALESCE () in a function to use it, or you can optimize the performance by placing it in a separate computing column, and always get results.

Author: