Snippets

Home / Snippets / Cursor Action Without a Cursor

Cursor Action Without a Cursor

Snippet Menu

snippet |

revisions |

comments | Related Snippets

related |

Add to Favorites | Email

download | Print

print | Blog It

blog it

Wed. Feb. 28th, 2007 1:11 PM

1 of 1

Idlemind

MSSQL | mysql

0 comments

When you need to perform an action on a single row of data, many people turn to cursors. Cursors come with a lot of overhead in the database engine and can be confusing. Here's a way to get the same results without the overhead in a much simplier way.

-- Declare counter variables

-- @min will be the "current" record being examined

-- @max is the highest record.

declare @min int,

        @max int

-- Declare a variable to hold the name of the table:

declare @tablename sysname

-- Set the initial values for the counter variables

-- @min is the lowest record meeting our condition,

-- @max is the highest:

SELECT @min = min(id),

       @max = max(id)

  FROM sysobjects

 WHERE type = 'u'

-- Establish loop conditions:

-- Continue running until @min is equal to @max

while @min <= @max

   begin

   -- Populate the tablename variable based on

   -- the current value of @min

   SELECT @tablename = name

     FROM sysobjects

    WHERE id = @min

   -- Execute the DBCC (or other operation)

   dbcc checktable (@tablename)

   -- Advance the counter.

   -- Note the where condition is identical to the

   -- initial loop condition with one additional

   -- bit: "and id > @min"

   SELECT @min = min(id)

     FROM sysobjects

    WHERE type = 'u'

      AND id > @min

   -- Exit the loop

   end

While this example uses MS SQL and the sysobjects table, it is easy enough to adjust the basic structure to MySQL or any other table that has an auto-incrementing, unique integer. If you don't have one of those, or you want to sort the records some other way, create a temp table with it's own auto-incrementing integer and the Primary Key of the table you want to adjust (make sure you drop the temp table when you're finished). I am a firm believer that a cursor is never the right answer. This is a much simpler and cleaner way to achieve the same basic result without all the extra over-head a cursor places on the database engine.