Search code examples
sqlsql-server-2012syntax-errorcursors

Why no parentheses on this cursor declaration?

I have a cursor declaration here:

declare c cursor
for     (select ProductName, ListPrice
        from Products
        where ListPrice > 700)

But if I add an order by clause, I get an error:

declare c cursor
for     (select ProductName, ListPrice
        from Products
        where ListPrice > 700
        order by ListPrice desc)

The error: Incorrect syntax near the keyword 'order'.

But the error goes away if I take away the parentheses:

declare c cursor
for     select ProductName, ListPrice
        from Products
        where ListPrice > 700
        order by ListPrice desc

Perhaps I'm a bit unclear about what the parentheses do in SQL Server. What gives? Why would the order by clause interact with the parentheses in that way?

Solution

  • order by is not allowed in subqueries, as noted in the comments. Wrapping the select in parentheses makes SQL interpret it as a subquery.