SQL Server 2005/2008 : The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Today I was trying to write a TSQL script which was using a cursor. In the DECLARE statement of the cursor I was trying to use the ORDER BY clause.
DECLARE cursor TABNAME
Select object_id, schema_id, name from sys.tables order by name desc
I received the above mentioned error while running the TSQL. It seems this was a bug while creating a view and has been fixed.
And there are alternatives to the hotfix. You can use the above select in a subquery and use the TOP 100 PERCENT option
Select TOP 100 percent * from (select object_id, schema_id, name from sys.tables order by name desc)
But the TOP method seems to work fine while creating views and does not work for my cursor problem.
So I found a better alternative by not ordering the data while declaring the cursor but in fact using it at the end of TSQL.
So once I had processed all the values in the cursor, I had a temporary table with the end result which also included the table name. So I selected from this temporary table and then ordered them. I am sure this might not work for everyone but it was just one of the many options that I thought about.
If you find an option to use the ORDER BY clause in the cursor then please let me know.