When we use the OUTPUT clause in SQL Server, we have the option of providing expressions instead of just the column names. For example, we could use an expression that compares the old price with the new price and returns the difference. Such data could be handy, depending on what your goals are.

Let’s dive straight into an example that uses an expression in the OUTPUT clause.

Example

Suppose we create the following table and populate it with data:

-- Create a sample table
CREATE TABLE Products (
    ProductID INT IDENTITY, 
    Name NVARCHAR(50), 
    Price DECIMAL(10,2)
    );

-- Insert initial data
INSERT INTO Products (Name, Price)
VALUES 
    ('Product A', 9.99), 
    ('Product B', 35.50),
    ('Product C', 29.75), 
    ('Product D', 17.70);

Now let’s make an update, and use the OUTPUT clause to return information about the rows that were updated:

UPDATE Products
SET Price = Price * 1.20
OUTPUT 
    INSERTED.Name, 
    DELETED.Price AS OldPrice,
    INSERTED.Price AS NewPrice,
    (INSERTED.Price - DELETED.Price) AS PriceDifference
WHERE Name IN ('Product B', 'Product C');

Output:

Name       OldPrice  NewPrice  PriceDifference
---------  --------  --------  ---------------
Product B  35.5      42.6      7.1            
Product C  29.75     35.7      5.95           

We can see that the PriceDifference column uses an expression that computes the difference in between the old price and the new price. It’s basically a computed column.

While we’re at it, notice that I used aliases for three of the columns. Here’s what happens if I remove the aliases:

UPDATE Products
SET Price = Price * 1.20
OUTPUT 
    INSERTED.Name, 
    DELETED.Price,
    INSERTED.Price,
    (INSERTED.Price - DELETED.Price)
WHERE Name IN ('Product B', 'Product C');

Output:

Name       Price           
---------  ----------  ----
Product B  42.6,51.12  8.52
Product C  35.7,42.84  7.14

The column with the expression doesn’t get a column header. Not only that, but the two price columns now share a column. Their values are returned in the same column, separated by a comma.

So be sure to include column aliases if you want expressions to have a column header and for each column to remain in a separate column.

Also note that any expressions in the OUTPUT clause must be scalar. They can include any combination of symbols and operators that evaluates to a single value. Aggregate functions aren’t permitted in the OUTPUT clause.

Also, any columns referenced in the expression must be qualified with either DELETED or INSERTED. This tells SQL Server which value you’re referring to – the old value or the new one.