Cursor and order by

I noticed that I cannot use ORDER BY in cursor, but in my case I need to order data in cursor.
I'm thinking about selecting data for cursor from temp table where data is ordered but may be there are other decisions to get data in cursor ordered?

declare CR cursor ...

for

select ..

from ...

order by ...

|||This query works:
select MembersElectroValues.Date,
MembersElectroValues.StartValue,
MembersElectroValues.CurrentValue,
(MembersElectroValues.CurrentValue - MembersElectroValues.StartValue) as [Difference]
from MembersElectroValues where UserID = 11 order by [Date]

Works

Now I try

declare @.MembersValues cursor
set @.MembersValues = cursor for (select MembersElectroValues.Date,
MembersElectroValues.StartValue,
MembersElectroValues.CurrentValue,
(MembersElectroValues.CurrentValue - MembersElectroValues.StartValue) as [Difference]
from MembersElectroValues where UserID = 11 order by [Date])

And I get message "Incorrect syntax near the keyword 'order'."
I use SQL server 2005 Express

|||

It doesn't like the parens around the select. It gives the error message in the wrong spot. It is not obvious what the parser is thinking.

By the way, Cursors are rarely required. Have you considered using a set query instead?

|||I didn't get an error when running without parents. But it's still paradox why it's working properly while running without ORDER BY with parents.

Thanks for the help|||

In the second case, you need to quote the cursor declare statement as a string.

declare @.MembersValues cursor
set @.MembersValues = N'cursor for (select MembersElectroValues.Date,
MembersElectroValues.StartValue,
MembersElectroValues.CurrentValue,
(MembersElectroValues.CurrentValue - MembersElectroValues.StartValue) as [Difference]
from MembersElectroValues where UserID = 11 order by [Date])'

|||

No. This is not valid syntax. See the SET statement topic in BOL for more details.

http://msdn2.microsoft.com/en-us/library/ms189484.aspx