Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106

    Unanswered: Problem with cursor and "order by"

    I need to create a cursor in a specific order but when I set the "ORDER BY"-statement in my cursor-declaration but it gives me this error:

    Incorrect syntax near the keyword 'ORDER'.

    When I remove the order-statement it works just great. I have seen this beeing referenved in BOL and on several websites, but I can't get it to work. Any ideas...?
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    could you post your cursor declaration? both working and non-working.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    Sure thing...

    Working ->
    Code:
      SET @Cur = CURSOR FOR (SELECT BallotID, bob_ballot.UserID, Max_Volume, Balance 
        FROM bob_ballot, bob_user
        WHERE bob_ballot.UserID = bob_user.UserID
        AND Odds = @Odds
        AND DescID = @DescID
        AND WinnerAlt = @PlayerID
        AND Single = 1
        AND Request IS NOT NULL)
    Not working ->

    Code:
      SET @Cur = CURSOR FOR (SELECT BallotID, bob_ballot.UserID, Max_Volume, Balance 
        FROM bob_ballot, bob_user
        WHERE bob_ballot.UserID = bob_user.UserID
        AND Odds = @Odds
        AND DescID = @DescID
        AND WinnerAlt = @PlayerID
        AND Single = 1
        AND Request IS NOT NULL
        ORDER BY Odds DESC)
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  4. #4
    Join Date
    Jan 2003
    Location
    British Columbia
    Posts
    44
    I have no idea why this works but either get rid of the brackets or move the order by clause after the last bracket.

    Code:
    SET @Cur = CURSOR FOR (SELECT BallotID, bob_ballot.UserID, Max_Volume, Balance 
        FROM bob_ballot, bob_user
        WHERE bob_ballot.UserID = bob_user.UserID
        AND Odds = @Odds
        AND DescID = @DescID
        AND WinnerAlt = @PlayerID
        AND Single = 1
        AND Request IS NOT NULL)
        ORDER BY Odds DESC

  5. #5
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106

    Cool

    Yeeeehaaa! You're a geniuos! You better belve it, you totally saved my day!
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  6. #6
    Join Date
    May 2002
    Posts
    299

    Re: Problem with cursor and "order by"

    You might want to post your code here so we can see.

    This example works just fine...

    declare cc cursor
    for select orderid
    from northwind..orders
    order by customerid

    deallocate cc
    --
    -oj
    http://www.rac4sql.net

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •