Reputation: 563
Having defined a view in SQL Server 2005, I want to write a stored procedures which returns the top n elements from this view according to some sorting criteria. However, before returning them I must do some updates to these results, but I can't figure out how to do it without querying the view twice... My solution would be
CREATE PROCEDURE UpdateAndReturn
AS
UPDATE TableToUpdate SET Field = @Something WHERE IDRef IN (SELECT TOP (n) ID FROM View ORDER BY This, That)
SELECT TOP (n) * FROM View ORDER BY This, That
GO
Can anyone help me finding anything more elegant and efficient than this?
Upvotes: 1
Views: 57
Reputation: 139010
Query the view first and put the result to a table variable or a temporary table. Use that table in your update statement and finally do select *
from the temp table.
Upvotes: 2