1,105,423 Community Members

"order by" in a cursor

Member Avatar
jrosh
Junior Poster
194 posts since Sep 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
JorgeM
IT Addict
6,400 posts since Dec 2011
Reputation Points: 581 [?]
Q&As Helped to Solve: 958 [?]
Skill Endorsements: 172 [?]
Moderator
Featured
Sponsor
 
0
 

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)

Member Avatar
jrosh
Junior Poster
194 posts since Sep 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
JorgeM
IT Addict
6,400 posts since Dec 2011
Reputation Points: 581 [?]
Q&As Helped to Solve: 958 [?]
Skill Endorsements: 172 [?]
Moderator
Featured
Sponsor
 
0
 

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
You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article