Introduction

A common requirement in database code is to update a row and return both the old value and the new value in the same operation. That usually comes up in audit logging, optimistic UI updates, or APIs that want to confirm what changed without issuing a second query. The safest solution is to capture the old and new versions inside the update statement itself rather than reading once and updating later in separate steps.

Use the Database Engine to Capture Both Versions

If you run a SELECT to get the current value and then perform an UPDATE, another session can change the row in between. That gives you a race condition. A better pattern is to let the database return both images of the row during the update.

In SQL Server, the OUTPUT clause is designed for this. It exposes the pre-update row through deleted and the post-update row through inserted.

1CREATE TABLE Products (
2    ProductId INT PRIMARY KEY,
3    Name NVARCHAR(100) NOT NULL,
4    Price DECIMAL(10, 2) NOT NULL
5);
6GO
7
8INSERT INTO Products (ProductId, Name, Price)
9VALUES (1, 'Keyboard', 49.99);
10GO

Return Previous and Updated Values From a Stored Procedure

For a single-row update, one straightforward pattern is to write the OUTPUT rows into a table variable and then return them at the end of the procedure.

1CREATE OR ALTER PROCEDURE UpdateProductPrice
2    @ProductId INT,
3    @NewPrice DECIMAL(10, 2)
4AS
5BEGIN
6    SET NOCOUNT ON;
7
8    DECLARE @Changed TABLE (
9        ProductId INT,
10        PreviousPrice DECIMAL(10, 2),
11        UpdatedPrice DECIMAL(10, 2)
12    );
13
14    UPDATE Products
15    SET Price = @NewPrice
16    OUTPUT
17        deleted.ProductId,
18        deleted.Price,
19        inserted.Price
20    INTO @Changed (ProductId, PreviousPrice, UpdatedPrice)
21    WHERE ProductId = @ProductId;
22
23    SELECT ProductId, PreviousPrice, UpdatedPrice
24    FROM @Changed;
25END;
26GO

Calling the procedure returns both values from the actual update, not from a separate lookup.

EXEC UpdateProductPrice @ProductId = 1, @NewPrice = 59.99;

That result set can be consumed directly by application code.

Returning Values With Output Parameters

If you know the procedure affects at most one row and your calling code prefers output parameters, you can still capture the values through the OUTPUT clause and assign them after the update.

1CREATE OR ALTER PROCEDURE UpdateProductPriceWithOutputs
2    @ProductId INT,
3    @NewPrice DECIMAL(10, 2),
4    @PreviousPrice DECIMAL(10, 2) OUTPUT,
5    @UpdatedPrice DECIMAL(10, 2) OUTPUT
6AS
7BEGIN
8    SET NOCOUNT ON;
9
10    DECLARE @Changed TABLE (
11        PreviousPrice DECIMAL(10, 2),
12        UpdatedPrice DECIMAL(10, 2)
13    );
14
15    UPDATE Products
16    SET Price = @NewPrice
17    OUTPUT deleted.Price, inserted.Price
18    INTO @Changed (PreviousPrice, UpdatedPrice)
19    WHERE ProductId = @ProductId;
20
21    SELECT TOP (1)
22        @PreviousPrice = PreviousPrice,
23        @UpdatedPrice = UpdatedPrice
24    FROM @Changed;
25END;
26GO

1DECLARE @Old DECIMAL(10, 2);
2DECLARE @New DECIMAL(10, 2);
3
4EXEC UpdateProductPriceWithOutputs
5    @ProductId = 1,
6    @NewPrice = 69.99,
7    @PreviousPrice = @Old OUTPUT,
8    @UpdatedPrice = @New OUTPUT;
9
10SELECT @Old AS PreviousPrice, @New AS UpdatedPrice;

This style is useful when the caller already expects scalar outputs.

Handle Missing Rows and Business Rules Explicitly

A stored procedure should make it obvious when no row matched the requested key. Otherwise the caller may see NULL outputs and not know whether that means "not found" or "updated to null".

1CREATE OR ALTER PROCEDURE UpdateProductPriceChecked
2    @ProductId INT,
3    @NewPrice DECIMAL(10, 2)
4AS
5BEGIN
6    SET NOCOUNT ON;
7
8    DECLARE @Changed TABLE (
9        PreviousPrice DECIMAL(10, 2),
10        UpdatedPrice DECIMAL(10, 2)
11    );
12
13    UPDATE Products
14    SET Price = @NewPrice
15    OUTPUT deleted.Price, inserted.Price
16    INTO @Changed (PreviousPrice, UpdatedPrice)
17    WHERE ProductId = @ProductId
18      AND Price <> @NewPrice;
19
20    IF NOT EXISTS (SELECT 1 FROM @Changed)
21    BEGIN
22        THROW 50001, 'No row was updated.', 1;
23    END;
24
25    SELECT PreviousPrice, UpdatedPrice
26    FROM @Changed;
27END;
28GO

The Price <> @NewPrice predicate is optional, but it can help distinguish a real change from a no-op.

Why This Pattern Is Better Than Separate Queries

Capturing deleted and inserted values inside the update gives you three advantages:

• it avoids race conditions between read and write
• it works naturally with transaction boundaries
• it scales to multiple changed columns, not just one field

If you later need audit logging, the same OUTPUT data can be inserted into an audit table instead of or in addition to returning it to the caller.

Common Pitfalls

A common mistake is doing SELECT current_value and then UPDATE in separate statements. That looks simple, but it can return stale "previous" values under concurrent writes.

Another problem is using output parameters for a statement that can update multiple rows. Output parameters only represent one scalar value, so a result set or audit table is a better fit when more than one row may change.

Developers also forget to handle the case where no row matched the WHERE clause. That makes error handling ambiguous for the caller.

Finally, keep the solution database-specific. The SQL Server OUTPUT deleted and inserted pattern is excellent here, but other engines use different syntax such as RETURNING or triggers. Do not assume the exact same procedure body is portable across vendors.

Summary

• Capture old and new values inside the UPDATE, not with separate read and write steps.
• In SQL Server, use the OUTPUT clause with deleted and inserted.
• Return a result set for multi-row changes and output parameters for true single-row cases.
• Handle no-op or not-found cases explicitly.
• Use the same captured values for both API responses and audit logging when needed.