Getting The ORDER BY clause is invalid in views, unless TOP or FOR XML is also specified

I have this SQL Server stored procedure where I am returning an XML type output, however before that I will be updating the corresponding records as well.

ALTER PROCEDURE [dbo].[GetPublishedData](@PublicationURL varchar(100),@Number int) AS IF (@Number = 0) BEGIN BEGIN TRANSACTION TRAN2 SELECT 1 AS Tag, NULL AS Parent, NULL AS [root!1!], NULL AS [Item!2!Id], NULL AS [Item!2!Action], NULL AS [Item!2!Pub_Id], NULL AS [Item!2!Item_Id], NULL AS [Item!2!Item_type], NULL AS [Item!2!Last_Published_Date], NULL AS [Item!2!Url], NULL AS [Item!2!Schema_Id] UNION SELECT 2, 1, '1', T.ID, T.ACTION, T.Pub_Id, T.Item_Id, T.ITEM_TYPE, T.LAST_PUBLISHED_DATE, T.URL, T.SCHEMA_ID FROM DBO.AUTN_ITEMS T WHERE FLAG = 0 AND ACTION IN ('ADD','UPD') AND URL LIKE @PublicationURL + '%' ORDER BY [Item!2!Last_Published_Date] ASC FOR XML EXPLICIT /*Update the FLAG with 1*/ UPDATE DBO.AUTN_ITEMS SET FLAG = 1 WHERE ID IN (SELECT ID FROM DBO.AUTN_ITEMS AT WHERE AT.FLAG = 0 AND ACTION IN ('ADD','UPD') AND AT.URL LIKE @PublicationURL + '%' ORDER BY AT.LAST_PUBLISHED_DATE ASC) COMMIT TRANSACTION TRAN2 ENDRETURN

I have two questions:

How to use Order by in my Update SQLIs there better way to write above query where I can simply pass the transaction id for updating the records

Please suggest!!

View the original article here