T-SQL: Get Updated Value

There are many ways to get the value that a column has just been updated to. Often, it'll be being set to an explicit value that you already know, but there are occasions (eg GETDATE() and using Functions) where you might not know for sure what the value has been updated to.

You could use the OUTPUT clause available in SQL2005 and later and for auto-increments you have SCOPE_IDENTITY and friends.

However, here's a neat little trick that I've seen once or twice before and involves setting the variable in the SET clause of your UPDATE statement and looks like this:

CREATE TABLE dbo.Beatles
(IdVal INT, Nm NVARCHAR(55))
GO
INSERT INTO dbo.Beatles SELECT 1, 'John'
INSERT INTO dbo.Beatles SELECT 2, 'Paul'
INSERT INTO dbo.Beatles SELECT 3, 'George'
INSERT INTO dbo.Beatles SELECT 4, 'Ronny'
GO
DECLARE @NewName NVARCHAR(55)

UPDATE dbo.Beatles
SET @NewName = Nm = 'Ringo' -- look at this funky syntax!!
WHERE IdVal = 4

SELECT @NewName
SELECT * FROM dbo.BeatlesWHERE IdVal = 4

Ok, so its not going to set the world on fire, but its something that you may come across so can't hurt to be aware of.