/** write by Stone 2012.12.28 */
use tools
goif 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