Code generator - cursor loop over any table.

Download sample code: WizDeclareCursor.zip

Every time I need to write a loop I tend to copy/paste it from somewhile. Just too boring to declare all variables (corresponding to column names), FETCH, @@FETCH_STATUS etc. Here is a solution:

CREATE TABLE MyTable(
       number    int,
       string    varchar(30),
       date      datetime,
       string10  varchar(10),
       string20  varchar(20),
       string30  varchar(30),
       x xml)
GO

Then you run

exec wiz_DeclareCursor 'MyTable', 'hc1'

and get the results:

   declare @number                         int

   declare @string                         varchar(30)

   declare @date                           datetime

   declare @string10                       varchar(10)

   declare @string20                       varchar(20)

   declare @string30                       varchar(30)

   declare @x                              xml

   declare hc1 cursor STATIC LOCAL for

    select number, string, date, string10, string20, string30, x

      from MyTable

   open hc1

   while (1=1)

   begin

      fetch next from hc1

       into @number, @string, @date, @string10, @string20, @string30, @x

      if (@@fetch_status = -1) break

      if (@@fetch_status = -2) continue

   end

   close hc1

   deallocate hc1

You should only copy results from the bottom of the screen of QueryAnalyzer to the top and fill the body of cursor.