if exists (select * from sysobjects where id = object_id('dbo.sp_dclgen') and sysstat & 0xf = 4)
	drop procedure dbo.sp_dclgen
GO
create procedure sp_dclgen @objname varchar(92) = NULL
as
/********************************************************************
           sp_dclgen : Stored procedure to generate declare, open,
                       fetch, close code for a user table, user view
                       or system table
          Parameters : @objname - Table name for cursor code
                Flow : Validate Objname Input Parameter
                     : Prepare temporary table
                     : Populate temporary table with column attributes
                     : Cursor through temporary table
                     :     Print declare, open, fetch, close code
             Returns : 0 - failed
                     : 1 - success
            Platform : MS Sql Server 6.5 sp4
*********************************************************************/
declare @objid   int          /* id of the object                 */
declare @sysstat smallint     /* the type of the object           */
declare @dbname  varchar(30)  /* name of current database         */
declare @colname varchar(30)  /* column name                      */
declare @msg     varchar(255) /* General purpose message variable */
declare @msg2    varchar(255) /* General purpose message variable */
set nocount on
/*
**  If no @objname given, give a little info about all objects.
*/
if @objname is null
  begin
    print 'usage: sp_dclgen <table name|view name>'
    return(0)
  end
/*
**  Make sure the @objname is local to the current database.
*/
if @objname like '%.%.%' and  substring(@objname, 1, charindex('.', @objname) - 1) <> db_name()
  begin
    raiserror(15250,-1,-1)
    return(1)
  end
/*
**  Now check to see if the @objname is in sysobjects.
*/
select @objid = id
     , @sysstat = sysstat
  from sysobjects
 where id = object_id(@objname)
   and type in ('S','U','V')
/*
**  It wasn't in sysobjects so report error and exit
*/
if @objid is null
  begin
    select @msg = @objname + ' was not found.'
    print @msg
    return(0)
  end
/*
**  If the object is a system table, view, or user table, we want to check
**  out the object's columns here.
*/
if @sysstat & 0xf in (1, 2, 3)   -- system table, view, or user table.
  begin
    create
     table #sphelptab
         ( col_name         char (30)   NULL
         , col_type         char (30)   NULL
         , col_len          tinyint     NULL
         , col_prec         char (5)    NULL
         , col_scale        char (5)    NULL
         , col_status       tinyint     NULL
         , colid            tinyint     NULL
         , type_systemdata  tinyint     NULL
         )
    insert
      into #sphelptab
    select c.name
         , t.name
         , c.length
         , convert(char(5),c.prec)
         , convert(char(5),c.scale)
         , c.status
         , c.colid
         , t.type
      from syscolumns c
         , systypes t
     where c.id = @objid
       and c.usertype *= t.usertype
/*
** Don't display precision and scale for datatypes
** for which they not applicable.
*/
    update #sphelptab
       set col_prec = ''
         , col_scale = ''
     where col_type in (select name
                          from systypes
                         where type not in (38,48,52,55,56,59,60,62,63,106,108,109,110,122))
/*
** print out declare statements
*/
    declare w cursor
        for select 'declare @'+ col_name
                 , case
                     when col_type = 'varchar' then
                       'varchar(' + convert(varchar(5), col_len) + ')'
                     when col_type ='char' then
                       'char(' + convert(varchar(5), col_len) + ')'
                     when col_type ='decimal' then
                       'decimal(' + col_prec + ',' + col_scale  + ')'
                     else
                       col_type
                   end
              from #sphelptab
             order
                by colid
    open w
    fetch next
     from w
     into @msg
        , @msg2
    while (@@fetch_status = 0)
      begin
        select @msg = convert(char(50),@msg) + @msg2
        print @msg
        fetch next
         from w
         into @msg
            , @msg2
      end
    close w
    deallocate w
    select @msg = convert(char(50),'declare ' + @objname + '_cursor') + 'cursor'
    print @msg
    declare x cursor
        for select col_name
              from #sphelptab
             order
                by colid
    open x
    fetch next
     from x
     into @colname
    if (@@fetch_status = 0)
      begin
        select @msg = '    for select ' + @colname
        print @msg
        fetch next
         from x
         into @colname
        while (@@fetch_status = 0)
          begin
            select @msg = '             , ' + @colname
            print @msg
            fetch next
             from x
             into @colname
          end
        select @msg = '          from ' + @objname
        print @msg
      end
    close x
    deallocate x
    select @msg = 'open ' + @objname + '_cursor'
    print @msg
    declare y cursor
        for select col_name
              from #sphelptab
             order
                by colid
    open y
    fetch next
     from y
     into @colname
    if (@@fetch_status = 0)
      begin
        print 'fetch next'
        select @msg = ' from ' + @objname + '_cursor'
        print @msg
        select @msg = ' into @' + @colname
        print @msg
        fetch next
         from y
         into @colname
        while (@@fetch_status = 0)
          begin
            select @msg = '    , @' + @colname
            print @msg
            fetch next
             from y
             into @colname
          end
      end
    close y
    deallocate y
    print 'while (@@fetch_status = 0)'
    print '  begin'
    print '    /* execute logic */'
    declare z cursor
        for select col_name
              from #sphelptab
             order
                by colid
    open z
    fetch next
     from z
     into @colname
    if (@@fetch_status = 0)
      begin
        print '    fetch next'
        select @msg = '     from ' + @objname + '_cursor'
        print @msg
        select @msg = '     into @' + @colname
        print @msg
        fetch next
         from z
         into @colname
        while (@@fetch_status = 0)
          begin
            select @msg = '        , @' + @colname
            print @msg
            fetch next
             from z
             into @colname
          end
      end
    close z
    deallocate z
    print '  end'
    select @msg = 'close ' + @objname + '_cursor'
    print @msg
    select @msg = 'deallocate ' + @objname + '_cursor'
    print @msg
  end
return(1)
GO