Results 1 to 7 of 7

Thread: Dynamic Cursor

  1. #1
    Join Date
    Jan 2009
    Posts
    47

    Unanswered: Dynamic Cursor

    How do i use dynamic Select query on Cursor for...
    Declare @Type varchar(50)
    SET @Type='DbBak'

    DECLARE db_cursor CURSOR FOR
    IF @Type='DbBak'
    SELECT [name] FROM sys.Databases WHERE [name] NOT IN ('tempdb')
    ELSE IF @Type='ReIndex'
    SELECT [Name] FROM sys.Databases Where [Name] Like ('Db%')

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @Name

    WHILE @@FETCH_STATUS = 0

    BEGIN
    ......

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I really wouldn't bother with a it-does-everything admin procedure like this. Have one procedure for backing up databases, and one for reindexing. Reuse code where it makes sense but not when it will ramp the complexity up loads.

    Whatever the heck you do, please don't try to use cursors in dynamic SQL.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2009
    Posts
    47
    Hey pootle flump,
    its our requirement so i asked some one who is really export in sql.
    Anyway thanks for response.

    Quote Originally Posted by pootle flump
    I really wouldn't bother with a it-does-everything admin procedure like this. Have one procedure for backing up databases, and one for reindexing. Reuse code where it makes sense but not when it will ramp the complexity up loads.

    Whatever the heck you do, please don't try to use cursors in dynamic SQL.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your requirement is to create dynamic cursors? Seriously?
    I really think you should consider a different question:
    "Can anyone tell me how to achieve <state your requirement here> without using dynamic cursors?"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2009
    Posts
    1
    A dynamic cursor, on the other hand, makes database changes available as they happen. Because the dynamic cursor requires the database provider to reorder data in the Recordset as it changes on the server, it is much more expensive than the static cursor in terms of the processing it requires. Also, because the order of the underlying records could change, only relative positioning is available to dynamic cursors. Lastly, dynamic cursors dont support bookmarks. If you need bookmark support, use a keyset or static cursor.

    ____________
    wasserbett
    Silicon Valley Outsourced CFO

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Moderators: Please see the moderators forum before acting on this thread.

    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Solved by the interwebz' most handsomest flump right here:
    SQL Server Forums - Cursor Open
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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