Results 1 to 11 of 11

Thread: Cursors

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Cursors

    Trying to understand cursors a little better, found this in one of the dbs I inherited. Just trying ot figure out why they put it there cause no one else knows anything about it.
    Code:
    DECLARE [TM #] CURSOR
        FOR SELECT * FROM [2004 TERMS];

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Before anything else, get your flame retardant suit out. Cursors almost always provoke a reaction around here.

    Cursors are a way of stepping through a result set "row by row". SQL Server was never optimized for that sort of processing, so any manual you read about cursors will usually start off by saying not to use them, and try your best to find a set based solution.

    If the previous programmer did his job right, you should see a while loop somewhere below the cursor declaration. Something like
    Code:
    while @@fetch_stats = 0
      begin
        ...
        fetch next into ...
      end
    Either that, or a comment about removing the cursor, if the customer finds performance is slowing down. Does this help?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Cursors are most handy for administrators who get paid by the hour and coders who get paid by the line.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT * in a cursor?

    I didn't think (and that happens all the time) you could do that....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Bad code has no limitations on it, Brett.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm stunned

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99(Col1 int, Col2 char(1))
    GO
    
    INSERT INTO myTable99(Col1,Col2)
    SELECT 1,'A' UNION ALL
    SELECT 2,'B' UNION ALL
    SELECT 3,'C'
    GO
    
    DECLARE myCursor99 CURSOR FOR SELECT * FROM myTable99
    
    DECLARE @col1 int, @col2 char(1)
    
    OPEN myCursor99
    
    FETCH NEXT FROM myCursor99 INTO @Col1, @Col2
    
    WHILE @@FETCH_STATUS = 0
      BEGIN
    	SELECT @Col1, @Col2
    	FETCH NEXT FROM myCursor99 INTO @Col1, @Col2
      END
    
    CLOSE myCursor99
    DEALLOCATE myCursor99
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    OK, want to hurt your brain a bit? Now imagine a select * cursor that adds columns to its own base table.

    No, I don't actually have one, and I am not certain it would work (schema locks to the rescue), but you know someone has given it serious thought.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by MCrowley
    Now imagine a select * cursor that adds columns to its own base table.

    Now what does that mean?

    And did you find any buffet tickets at face value?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Just saying it can always get worse.

    I can find Buffet tickets, but the face values are awfully ugly.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Just finished reviewing a stored procedure here that a "genius" spent a while creating...
    Code:
    ...
    	declare @<variable> money
    	set @<variable> = 0
    	declare p cursor forward_only
    	for select <field_name>
    		 from <table_name>
    		where <field_name1> = @<variable1>
    		  and <field_name2> = @<variable2>
    	open p
    	fetch p into @<variable>
    	close p
    	deallocate p
    ...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I can honestly say that I doubt that anything quite like that would have ever occured to me. I've learned something, I can go home happy now!

    -PatP

Posting Permissions

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