Sometimes you need to update data in a table and then update data in another table but based on a filter condition found from the first table. Specifically have you had to do this in the past?
-- ... UPDATE Users SET Verified = 1 FROM Logins WHERE EmailAddress = @EmailAddress DECLARE @UserId INT; SELECT TOP 1 @UserId = UserId FROM Logins WHERE EmailAddress = @EmailAddress UPDATE Users SET State = 2 -- Verified WHERE Id = @UserId -- ...
This is not only inefficient (from an execution plan perspective) but also prone to race conditions and requires more code. The simpler and safer alternative is to use the OUTPUT clause of the UPDATE.
Here’s how :
DECLARE @UserIdTable TABLE ( Id INT ); UPDATE Users SET Verified = 1 OUTPUT UserId INTO @UserIdTable FROM Logins WHERE EmailAddress = @EmailAddress DECLARE @UserId INT = SELECT TOP 1 Id FROM @UserIdTable; UPDATE Users SET State = 2 -- Verified WHERE Id = @UserId
In the above code sample I take advantage of the new declare and initialize syntax introduced in SQL Server 2008. The OUTPUT clause has been introduced in SQL Server 2005 so nothing here is really news.
Another simplification that I hoped it was possible was to avoid the declaration of the local table variable and just push the OUTPUT into the local variable (@UserId) but it seems you can’t.
I found out about the OUTPUT clause recently from Remus Rusanu’s blog post about implementing queues with tables. These have, usually, high concurrency and any race condition that might occur will occur. OUTPUT is usually the best way to solve it.