Creating a cursor to select in a particular order

Posted on 2007-07-29
Medium Priority
Last Modified: 2012-06-27
I need to cursor a table in a particular order because i am creating a new sort number that is to update the table.  Any ideas on how to do this.  The cursor is not liking the order by in the select statement for the cursor.  Here is what I have:

Declare curNum cursor

For (Select Number, subNumber From ##Table order by sortorder)

Declare @Num varchar (25)
Declare @subnum varchar (25)

Open curNum

Fetch Next from curNum Into @Num, @subnum
While (@@Fetch_Status = 0)

Then Statement
Fetch Next from curNum into @Num, @subnum

Close curNum
Deallocate curNum
Question by:griswald65
LVL 15

Expert Comment

ID: 19588293
You can't use an ORDER BY in the for section of a cursor. Just put this right before the cursor declaration:

select * into ##Table2 from ##Table order by sortorder

and then reference ##Table2 instead of ##Table
LVL 22

Accepted Solution

dportas earned 1500 total points
ID: 19588454
Remove the parentheses around the cursor declaration and it should work just fine. ORDER BY is perfectly valid in a cursor declaration and is the only documented way to set the order of a cursor. Note that Derekkromm's suggestion does not guarantee the resulting order because A) ORDER BY is irrelevant in a SELECT INTO statement so the results are undefined and often unpredictable, and B) The cursor won't necessarily respect the order in which rows were inserted unless ORDER BY is included in the declaration.

Having said all that, I suggest you probably don't need a cursor to achieve what you were suggesting. If you want to update the table based on some numbered order then take a look at the ROW_NUMBER function in Books Online. Cursors are rarely a good solution for most problems.


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question