The OUTPUT clause in SQL Server 2005

Posted by Dylan Beattie on 20 March 2008 • permalink One of those things that I can't believe I've come this far without knowing - or needing. There's a little-known clause you can use in your INSERT, UPDATE and DELETE statements in SQL Server 2005 that will let you return the affected rows within a single query.
Picture this: Hotmail gets shut down, and you need to remove all Hotmail addresses from your Customer table and create a list of the affected customers so you can phone them all and ask what their new e-mail address is. And, to make it interesting, you're trying to do it in a one stored procedure call.
CREATE PROCEDURE HotmailGoByeBye AS BEGIN
    UPDATE Customer SET Email = null WHERE Email LIKE '%hotmail.com'
    SELECT Name, Phone FROM Customer where Email LIKE '%hotmail.com'
END
Hands up if you can see why that's not going to work...
Now, check this out.
CREATE PROCEDURE HotmailGoByeBye AS BEGIN
    UPDATE Customer
    SET Email = null
    OUTPUT inserted.Name, inserted.Phone
    WHERE Email LIKE '%hotmail.com'
END
The inserted keyword there works just like it does in triggers - i.e. it contains all the rows affected by the update after they've been updated. It can do a lot more besides - Books Online has the full details as usual.
I'm incorporating this into some ORM code I'm working with, because it'll let me clear down any references to, say, the Address that's about to get deleted, return the affected records, update the corresponding in-memory objects so their timestamps are accurate, delete the address itself, and proceed with the rest of the batch update without lots of nasty timestamp violations.
Interestingly, even the much-requested SQL Server 2005 support for multiple cascade paths wouldn't alleviate this problem, since I'm using timestamps for concurrency control and ON CASCADE SET NULL works like a trigger - i.e. it modifies the records but doesn't return them to the calling application.