Do CURSORs work with ORDER BY?

Cursors are not liked by most DBAs, but sometimes they are necessary and for administrative tasks working well.
Sometimes you even want to have the set that is iterated in a specific order. Therefore you like to add an ORDER BY to your select statement. Most of us are not really familiar with the syntax of the whole thing so you google a bit and find code snippets for cursors.

DECLARE @DB VARCHAR(25)

DECLARE DBCursor CURSOR
FOR
    ( SELECT    [Name]
      FROM      master.dbo.sysdatabases
      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

You copy and paste them into your SSMS and wonder why you get the following error:

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'ORDER'.

You just mark the SELECT statement and execute it and it works perfectly.

So what’s the problem here?

It’s the parentheses around the SELECT statement.

    ( SELECT    [Name]
      FROM      master.dbo.sysdatabases
      ORDER BY  [DBID] DESC
    )

In BOL there is nothing about parentheses, so if you get rid of them, the cursor will work:

DECLARE @DB VARCHAR(25)

DECLARE DBCursor CURSOR
FOR
      SELECT    [Name]
      FROM      master.dbo.sysdatabases
      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
Advertisements
This entry was posted in SQL Server and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s