MSSQL2005下根据查询语句自动产生游标语句的脚本

/** write by Stone 2012.12.28 */

use tools

go
if object_id('xp_create_cursor_in') is not null
  drop proc xp_create_cursor_in
go
create proc xp_create_cursor_in 
  @p_szQryStmt varchar(255)
as
begin
  SET NOCOUNT ON
  DECLARE @SQLCMD VARCHAR(4096) 
  SET @SQLCMD = '
  DECLARE c_col cursor 
      FOR ' + @p_szQryStmt + ' ' + 
  'DECLARE @Report CURSOR
  EXEC master.dbo.sp_describe_cursor_columns @cursor_return = @Report OUTPUT,
      @cursor_source = N''global'', @cursor_identity = N''c_col''
  FETCH NEXT from @Report
  WHILE (@@FETCH_STATUS <> -1)
  BEGIN
    FETCH NEXT from @Report
  END
  CLOSE @Report
  DEALLOCATE @Report
  DEALLOCATE c_col '
  exec(@SQLCMD)
end
go


if object_id('xp_create_cursor') is not null
  drop proc xp_create_cursor
go
create proc xp_create_cursor 
  @p_szQryStmt varchar(255)
as
begin
  SET NOCOUNT ON
  create table #temp
  ( 
    column_name sysname,
    ordinal_position int,
    column_characteristics_flags int, 
    column_size int,
    data_type_sql smallint, 
    column_precision tinyint, 
    column_scale tinyint, 
    order_position int, 
    order_direction varchar(1),
    hidden_column smallint, 
    columnid int,
    objectid int, 
    dbid int, 
    dbname sysname 
  )
  DECLARE @SQLCMD VARCHAR(2048)
  SET @SQLCMD = ' INSERT INTO #temp exec xp_create_cursor_in ''' +  @p_szQryStmt  + ''''
  exec (@SQLCMD)
  -- select * from #temp
  declare @col table( column_name varchar(64), cnt int)
  declare @line table( recno int identity, line varchar(2048))
  declare @sql_fetch varchar(2048)
  declare @sql_declare varchar(2048), @var_name varchar(64), @cnt int
  declare @column_name varchar(64), @data_type_name varchar(64), @column_size int, @column_precision int, @column_scale int
  declare c_col cursor
      for 
   SELECT column_name, type_name(data_type_sql) as data_type_name, column_size, column_precision, column_scale FROM #temp a order by ordinal_position 
  set @sql_fetch = ''
  open c_col 
  fetch c_col into @column_name, @data_type_name, @column_size, @column_precision, @column_scale
  while @@fetch_status = 0
  begin
    set @column_name = '@' + @column_name
    set @var_name = @column_name
    if exists(select 1 from @col where column_name = @column_name)
    begin
      select @cnt = cnt from @col where column_name = @column_name
      select @var_name = @column_name + '_' + cast(@cnt as varchar(32))
      update @col set cnt = cnt + 1 where column_name = @column_name 
    end
    else
    begin
      insert into @col values(@column_name, 1)
    end
    if @data_type_name not in ('char', 'varchar', 'numeric')
    begin
      set @sql_declare = '  declare ' + @var_name + ' ' + @data_type_name 
    end
    else if  @data_type_name in ('char', 'varchar')
    begin
      set @sql_declare = '  declare ' + @var_name + ' ' + @data_type_name + '(' + cast(@column_size as varchar(32)) + ')'  
    end
    else
    begin
      set @sql_declare = '  declare ' + @var_name + ' ' + @data_type_name + '(' + cast(@column_precision as varchar(32)) + ',' + 
        cast(@column_scale as varchar(32)) + ')'    
    end
    -- print @sql_declare
    insert into @line (line) values( @sql_declare )  
    set @sql_fetch = @sql_fetch + @var_name + ', '
    -- 
    fetch c_col into @column_name, @data_type_name, @column_size, @column_precision, @column_scale
  end
    print @sql_fetch
  set @sql_declare = '  declare c_cursor cursor ' + char(10) + 
                     '      for ' + @p_szQryStmt 
  insert into @line(line) values( @sql_declare )
  insert into @line(line) values( '  open c_cursor')
  set @sql_declare = '  fetch c_cursor into ' + left(@sql_fetch, len(@sql_fetch)-1) 
  insert into @line(line) values( @sql_declare )
  insert into @line(line) values ('  while @@fetch_status = 0 ')
  insert into @line(line) values ('  begin ')
  set @sql_declare = '    select ' + left(@sql_fetch, len(@sql_fetch)-1) 
  insert into @line(line) values( @sql_declare )
  insert into @line(line) values( '    -- add you code here - begin --')
  insert into @line(line) values( '     ')
  insert into @line(line) values( '     ')
  insert into @line(line) values( '    -- add you code here - end --')
  set @sql_declare = '    fetch c_cursor into ' + left(@sql_fetch, len(@sql_fetch)-1) 
  insert into @line(line) values( @sql_declare )
  insert into @line(line) values ('  end ')
  insert into @line(line) values ('  close c_cursor ')
  insert into @line(line) values ('  deallocate c_cursor ')
  close c_col
  deallocate c_col
  select line from @line order by recno
END

GO

-- 使用方法示例：

xp_create_cursor 'select * from master..sysobjects '

GO