Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    Unanswered: Parenthesis in CURSOR declaration

    Don't know when I got into the habit, but I have always been defining cursors using parenthesis around the SELECT statment, like this

    Code:
    DECLARE test CURSOR FOR
    	(
    	select	AField
    	from	ATable
    	)
    The parenthesis kinda set-off the SELECT statment and make it easier for me to read.

    I have always noticed that SQL does not allow an ORDER BY clause in the SELECT of a cursor.

    At least that is what I had experienced.

    Today I looked on the Help pages for he DECLARE CURSOR statement and noticed an example with an ORDER BY clause. I tried the ORDER BY and again it failed.

    I noticed that the example did not have parenthesis around the SELECT statment, so I tried it without the parenthesis and, voila, the ORDER BY worked.

    Is there a simple explanation for this?

    Why allow parenthesis for one version of SELECT, but not another? Shouldn't it either reject the use of parenthesis, or, if it accepts them, accept whatever is appropriate for any SELECT statment in a cursor?


    I must be missing something very basic.

    Thanks in-advance for any explanations.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Order By is not allowed in subqueries.
    (Though there is a loophole that allows you to specify it if you also include a TOP clause.)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Hadn't thought of it as a subquery.

    Thanks for the simple explanation.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem lies in what can be ordered. The following code demonstrates this:
    Code:
    --  works as a statement
    
    SELECT spid
       FROM master.dbo.sysprocesses   
    GO
    --  works as a table valued expression
    
    (  SELECT spid
          FROM master.dbo.sysprocesses
    )
    GO
    --  Result set of statements can be ordered
    
    SELECT spid
       FROM master.dbo.sysprocesses   
       ORDER BY spid
    GO
    --  Result set of expressions can NOT be ordered
    
    (  SELECT spid
          FROM master.dbo.sysprocesses   
          ORDER BY spid				-- This is a syntax error
    )
    GO
    Statements that return result sets can be ordered, because this is a discrete step added to the plan. Subqueries are actually treated as expressions, and expressions can not be ordered because they have to be sytactically stocastic.

    The specification for "syntactically stochastic" is a tarball that took a long time for me to grok... It would seem that this means that the expression always returns the same result, but that isn't completely true. What I understand "syntactically stochastic" to mean now is that the expression can't be influenced by either the data or the schema, but the short answer is that using the ORDER clause SHOULD not be allowed even though Microsoft does allow ORDER BY to be used within subqueriies with the TOP clause (which is a pet peeve of mine) to support the demands of their customers.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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