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.

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 
for
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.

http://support.microsoft.com/kb/926292

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.