CODEWITHSUNDEEP
sql-server-2005stored-proceduresview
B.M
B.M

Reputation: 563

Update & Return from Stored Procedure withouth Repeating Query

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

Answers (1)

Mikael Eriksson
Mikael Eriksson

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

Related Questions