"order by" in a cursor

[jrosh]

SQL server 2012 query.

declare IDList cursor for (SELECT ID FROM [dbo].[News] WHERE PublishFromDate BETWEEN cast(@fromDate  as datetime) AND cast(@toDate as datetime)) order by PublishFromDate 

The above cursor wrks fine. But when add order by "desc" make it is not working.

 declare IDList cursor for (SELECT ID FROM [dbo].[News] WHERE PublishFromDate BETWEEN cast(@fromDate  as datetime) AND cast(@toDate as datetime)) order by PublishFromDate desc

please help with this
thank you in advance

[JorgeM]

Without going into SQL Mgmt Studio to give this a try myself, I beleive that in the first example, the order by is simply being ignored because the default ordering is exactly what you are tyring to do with the first example.

Try moving the order by PublishFromDate into the last parenthesis.

...(@fromDate as datetime) AND cast(@toDate as datetime) order by PublishFromDate Desc)

[jrosh]

Thanks for the reply. In management studio if i try as a new query this works fine even with the "order by desc" applied. But when i put

declare IDList cursor for (SELECT ID FROM [dbo].[News] WHERE PublishFromDate BETWEEN cast(@fromDate  as datetime) AND cast(@toDate as datetime) order by PublishFromDate desc)

into the cursor it exectutes successfully but no results are given.

[JorgeM]

Correct, you just declared the cursor. You would need to parse through the cursor for the results. I am by no means a SQL expert. I have had the need in the past to create cursors, and this is how I implemented them. Hope this helps...

DECLARE mySQLCursor CURSOR FOR
 SELECT field1, field2 from table1
 WHERE ID = @ id
 ORDER BY field2 desc

OPEN mySQLCursor
FETCH next FROM mySQLCursor INTO @field1Result, @field2Result
WHILE @@FETCH_STATUS = 0
BEGIN

 <-- Do stuff with @field1Result and @field2Result -->

FETCH next FROM mySQLCursor INTO @field1Result, @field2Result
END

CLOSE mySQLCursor
DEALLOCATE mySQLCursor