ORDER BY in a cursor

  • Is there a limitation on being able to order the select statement you are using for a cursor?

    In the query below, if I comment out the order by clause, it works, but if I leave it there it won't run.

    DECLARE @DB varchar(25)

    DECLARE DBCursor CURSOR FOR

    (

    SELECT [Name]

    FROM master.dbo.sysdatabases

    WHERE

    [NAME] LIKE '%PLUS' OR [NAME] LIKE 'BILLING%'

    ORDER BY [DBID] DESC

    )

    OPEN DBCursor

    FETCH NEXT FROM DBCursor INTO @DB

    WHILE @@fetch_status = 0

    BEGIN -- WHILE BEGIN

    PRINT @DB

    FETCH NEXT FROM DBCursor INTO @DB

    END -- WHILE END

    CLOSE DBCursor

    DEALLOCATE DBCursor

    The Redneck DBA

  • Try this:

    DECLARE @DB varchar(25)

    DECLARE DBCursor CURSOR FOR

    SELECT [name]

    FROM master.sys.databases

    WHERE

    [name] LIKE '%PLUS' OR [NAME] LIKE 'BILLING%'

    ORDER BY [database_id] DESC

    OPEN DBCursor

    FETCH NEXT FROM DBCursor INTO @DB

    WHILE @@fetch_status = 0

    BEGIN -- WHILE BEGIN

    PRINT @DB

    FETCH NEXT FROM DBCursor INTO @DB

    END -- WHILE END

    CLOSE DBCursor

    DEALLOCATE DBCursor

    😎

  • Hi,

    I believe cursor dont like order by. I am not sure. But i tried to run same thing and it didnt work. What you can do is before your cursor start you can enter your required data with order by into some other table. (temp table) and then use select statement from that table.

    thanks,

    vijay

  • Here you go

    CREATE PROCEDURE [dbo].[VD_TEMP_0506] AS

    DECLARE @DB varchar(25)

    create table #t ([name] varchar(50))

    insert into #t

    SELECT [Name]

    FROM master.dbo.sysdatabases

    Where [name] like '%NCR%'

    order by [Name]

    DECLARE DBCursor CURSOR FOR

    (

    SELECT [Name]

    FROM #t

    )

    OPEN DBCursor

    FETCH NEXT FROM DBCursor INTO @DB

    WHILE @@fetch_status = 0

    BEGIN -- WHILE BEGIN

    PRINT @DB

    FETCH NEXT FROM DBCursor INTO @DB

    END -- WHILE END

    CLOSE DBCursor

    DEALLOCATE DBCursor

    GO

  • ORDER BY in a cursor works. Copy my code and test it.

    😎

  • Hi,

    Yes Lynn. you are right. It works. I said i m not sure because i never come up with the situation.

    It is just bracket. ()

    DECLARE @DB varchar(25)

    DECLARE DBCursor CURSOR FOR

    SELECT [Name]

    FROM master.dbo.sysdatabases

    WHERE

    [NAME] LIKE '%PLUS' OR [NAME] LIKE 'BILLING%'

    ORDER BY [DBID] DESC

    OPEN DBCursor

    FETCH NEXT FROM DBCursor INTO @DB

    WHILE @@fetch_status = 0

    BEGIN -- WHILE BEGIN

    PRINT @DB

    FETCH NEXT FROM DBCursor INTO @DB

    END -- WHILE END

    CLOSE DBCursor

    DEALLOCATE DBCursor

    And it will work.

  • Curious, looking at your code, are you running SQL 2005 or SQL 2000?

    😎

  • I have both opened. We have some server on 2000 and some on 2005. so can use 2005 and enterprise manager as well.

    When i take jason's code i need to use 2000 and ur code - 2005.

  • Taking out the parentesis arround the select statement fixed it.

    The Redneck DBA

  • Thanks Lynn - good solutions can be so simple - and so unintuitive...

    P.

  • Please note: 5 year old thread.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Just because it is old doesn't mean some of us won't find it to be useful information.

    The Redneck DBA

  • Never said it would. Hopefully however it will prevent people wasting time trying to solve a problem that's been solved for 5 years (as I did this morning before I noticed the date)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Guys

    It saved me lots of time after a wasted afternoon. So, it's an oldie but goldie!

    P.

  • Jason Shadonix (7/9/2013)


    Just because it is old doesn't mean some of us won't find it to be useful information.

    Glad the thread helped you, but it is not necessary to say thanks 5 years after the fact.

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply