Results 1 to 8 of 8

Thread: Nested Cursors

  1. #1
    Join Date
    May 2004
    Location
    Ohio
    Posts
    61

    Unanswered: Nested Cursors

    Morning everyone,

    I have a sp that I've created that is to show me everyone table name and column name using nested cursors. However when I execute the procedure it doesn't show me the names, it just tells me the command completed successfully. Here is the code:

    CREATE PROCEDURE uspSeeAllViews
    AS
    SET NOCOUNT ON
    DECLARE @strMessage VARCHAR(100)
    DECLARE @strColumn VARCHAR(100)
    DECLARE @strView VARCHAR(100)
    DECLARE @strCommand VARCHAR(250)

    DECLARE crsViews CURSOR FOR

    SELECT
    name AS strView
    FROM
    sysobjects
    WHERE
    type = 'U'

    OPEN crsViews
    FETCH NEXT FROM crsViews INTO @strView
    WHILE @@FETCH_STATUS = 0 BEGIN

    DECLARE crsColumns CURSOR FOR

    SELECT
    name AS strColumn
    FROM
    syscolumns
    WHERE
    name = @strView

    OPEN crsColumns
    FETCH NEXT FROM crsColumns INTO @strColumn
    WHILE @@FETCH_STATUS = 0 BEGIN

    PRINT @strView + ':' + @strColumn
    FETCH NEXT FROM crsColumns INTO @strColumn
    END

    CLOSE crsColumns
    DEALLOCATE crsColumns

    FETCH NEXT FROM crsViews INTO @strView
    END

    CLOSE crsViews
    DEALLOCATE crsViews

    Thanks for looking, any ideas??

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto
    Posts
    28
    Instead of
    FETCH NEXT FROM crsViews INTO @strView

    try
    FETCH crsViews INTO @strView

    I haven't used NEXT FROM before, and i think you might be skipping records.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you are in "grid" mode within Query Analyzer, you might want to check the messages tab.

    Just FYI, you can combine both cursors into a single SELECT for much better performance and simplicity too.

    -PatP

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Think of nesting cursors as the database equivalent of shoving a cigarette into a cigar. It ain't healthy.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Why would you do this anyway????? I must be totally missing the boat on this one.

    1. You created two cursors, which are horrible on processor efficiency to give you something that SQL Server already does for you with the

    2. SELECT TABLE_NAME + ':' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
    --What's the difference here?

    If you ever do use cursors, which you should never do, you should have an overriding reason to do so.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Never is a pretty strong word. I have five or six occasions when cursors were required....in the last ten years!

    Cursors are a vb programmer's way of saying "Kilroy was here".
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yeah, but when you have to take a column and execute its contents, then cursors are really nice. Other than that, I don't really have much use for them.

    -PatP

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yup. I've had to do that...about five or six times...

    The one good thing about finding cursors in a client's code is that you can guarantee them that what you are developing will run faster than what they had. The assignment I'm on now is advising a client on moving OLAP onto a SQL Server platform. Their current system is based on DB2 and runs cobol code that creates and executes dynamic SQL loaded with cursor declarations.

    Yeah, I think this is gonna be faster...
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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