select @column_syntax_temp = case when @column_type_temp in ('datetime','image','int') then @column_name_temp + ' ' + @column_type_temp
when @column_type_temp in ('binary','bit','char','varchar') then @column_name_temp + ' ' + @column_type_temp + '('+convert(varchar(10),@column_length_temp) + ')'
else @column_name_temp + ' ' + @column_type_temp + '('+convert(varchar(10),@column_xprec_temp) + ',' + convert(varchar(10),@column_xscale_temp) + ')'
end
select @column_syntax = @column_syntax + @column_syntax_temp + ','
end
---处理栏位列表
if @temp_pos > 0
begin
select @select_command_temp = right(@select_command_temp,len(@select_command_temp) - @temp_pos)
end
else
begin
select @select_command_temp = ''
end
end
---形成正确的栏位创建语法
select @column_syntax = left(@column_syntax,len(@column_syntax) - 1)
---修改临时表的结构
execute('alter table #test add '+@column_syntax)
execute('alter table #test drop column a')
---将select执行的结构集插入到临时表
insert into #test
execute(@select_command)
---创建游标
set @cursor_return = cursor local scroll read_only for
select *
from #test
---打开游标
open @cursor_return
▲使用游标
/注:在select中有几项,fetch from @cursor_name into @cust_id就应该声明几个变量,而且顺序和类型必须一致.*/
declare @cursor_name cursor,
@select_command varchar(8000),
@cust_id varchar(20)
select @select_command = 'select cust_id from so_cust'
execute usp_createcursor @select_command,@cursor_name output
fetch from @cursor_name into @cust_id
while @@fetch_status = 0
begin
fetch from @cursor_name into @cust_id
end
close @cursor_name
