Creating a cursor to select in a particular order

Posted on 2007-07-29
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

    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

    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

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    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.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now