How to sort the result set of an UPDATE statement that uses the OUTPUT clause?

Question

I'd like to sort the rows returned by an UPDATE statement, but there's no direct way to do this according to the msdn page for the OUTPUT Clause, which says:

SQL Server does not guarantee the order in which rows are processed and returned by DML statements using the OUTPUT clause.

Since I can't just tack "order by" onto my original query, my next approach is to just put the results into a temporary table or table variable, and then select and order from that. I should be able to figure out a solution using temporary tables, but I don't think I've ever used a table variable before. Is there a reason to prefer one over the other in this context? Or is there a better way to get sorted results from an OUTPUT clause (i.e. w/out using a temp table or table variable)?

The DB platform is SQL Server 2005.

Here's the original query; I want to sort on the ReleaseDate field.

Update dbo.WFItem
Set ApprovedDate = getdate()
Output inserted.WFItemID, inserted.Title, inserted.Teaser
Where ApprovedDate is null

The table it runs against looks something like this:

CREATE TABLE [dbo].[WFItem](
    [WFItemID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [varchar](255) NULL,
    [Teaser] [varchar](255) NULL,
    [ReleaseDate] [datetime] NULL,
    [ApprovedDate] [datetime] NULL,
    [SentDate] [datetime] NULL,
 CONSTRAINT [PK_WFItem] PRIMARY KEY CLUSTERED 
(
    [WFItemID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

I guess your only option would be to turn the OUTPUT clause into an OUTPUT INTO and store the values in a temporary (in-memory) table and sort them there, when selecting from the temporary table.

DECLARE @UpdateOutputTable TABLE 
         (WFItemID INT, Title VARCHAR(255), Teaser VARCHAR(255), ReleaseDate DATETIME)

and then change your UPDATE statement to be:

Update dbo.WFItem
Set ApprovedDate = getdate()
Output inserted.WFItemID, inserted.Title, inserted.Teaser, inserted.ReleaseDate
    INTO @UpdateOutputTable 
Where ApprovedDate is null

and then select from the temporary table:

SELECT WFItemID, Title, Teaser
FROM @UpdateOutputTable
ORDER BY ReleaseDate

Marc

Answer 2

You can output results into table variable and then select from that table results in any order Here is an example from the same MSDN page that you referred to in your Q. but I added Order By in the end

DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25 
OUTPUT INSERTED.EmployeeID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar
Order by EmpID;