Simple SQL Cursor Example

Simple SQL Cursor Example

-- declare & open a cursor to get the names of the views in the database.
DECLARE curViews cursor fast_forward for
   select nm = OBJECT_NAME(a.[Object_id])
      , sch = OBJECT_schema_name(a.[Object_id])
   from sys.all_views a
OPEN curViews

-- declare some variables to hold the data from the cursor
declare @name sysname
   , @schema sysname

-- get the 1st record from the view.
FETCH next from curViews into @name, @schema

-- keep going while the last fetch was good
WHILE (@@FETCH_STATUS = 0)
begin

   -- do some stuff with the information retrieved from the cursor

   EndLoop:
   -- get the next record from the cursor
   FETCH next from curViews into @name, @schema
end

-- close & deallocate the cursor
CLOSE curViews
DEALLOCATE curViews

But, we all know that the use of cursors should be avoided whenever possible.