I think an easy way to do this functionality is using Output Clause. This Output clause can be used in Insert, Update and Delete statement. During this operation it will give us 2 pseudo tables called "inserted" and "deleted" tables. "Inserted" table will have the information after the query executed and "deleted" table will have the information before the query gets executes. So, you can achieve the same functionality using the below code.
UPDATE Product
SET ItemCount = ItemCount + 10
OUTPUT inserted.ProductId, deleted.ItemCount as [old count], inserted.ItemCount as [New count]
WHERE Price <= 5
Your output would be
ProductId , Old count, New count
(1 , 40 , 50)
(3 , 28 , 38)
If you want to use it inside your stored procedure and use the result for some other process in the same stored procedure then you may want to create some table variable at use the result for further process
declare @compare table
(
ProductId int not null,
[Old count] int,
[New count] int
)
UPDATE Product
SET ItemCount = ItemCount + 10
OUTPUT inserted.ProductId, deleted.ItemCount as [old count], inserted.ItemCount as [New count] INTO @compare
WHERE Price <= 5
SELECT * FROM @compare
Your output would be same as previous
ProductId , Old count, New count
(1 , 40 , 50)
(3 , 28 , 38)
Note: OUTPUT clause must be used after last SET and before WHERE Condition